.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Creating SSIS Package Variables Programmatically

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
 
Hi There, I am fairly new to SSIS.  I am creating a number of custom SSIS Tasks that I want to be able to share information between at runtime.  I figured that the best way to do this was through package variables, as in the Execute() method each custom task has access to the package variables through the VariableDispenser object.  I have managed to read variables at runtime that I create in the designer, however I am having trouble creating and writing to them.  I have created a very simple custom task below that I would expect to create the specified variable... [DtsTask(DisplayName = "TestVariableWriter")] public class Variab : Task { public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) { try { string varName = "TestVariable"; string varValue = "TestValue"; Variables vars = null; variableDispenser.LockForWrite(varName); variableDispenser.GetVariables(ref vars); if (variableDispenser.Contains(varName)) vars[varName].Value = varValue; else vars.Add(varName, false, string.Empty, varValue); vars.Unlock(); return DTSExecResult.Success; } catch (Exception ex) {


View Complete Post


More Related Resource Links

start SSIS package via script task with package configuration via variables

  

Hello,

it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!


Can a stored procedure programmatically determine if an SSIS package is running?

  

Greetings,

If I have the 'ExecutionInstanceGUID' value for an SSIS package that I know was started, is there a way that a stored procedure can programmatically determine whether the SSIS package still is running?

Thanks -

BCB

 


Get list of package variables in SSIS code

  

Hi all,

i need to get list of package variable names during execution of a custom control flow task.

Best regards Crom


Programmatically creating a package with a SAP .NET data provider: AcquireConnections fails

  

I'm trying to programmatically alter a package which uses a SAP .NET connector. When configuring and running the package from BIDS everything goes well. But when i try to call the AcquireConnections(vbNull) method from code , the following exception is thrown:

Unable to cast COM object of type 'System.__ComObject' to interface type 'System.Transactions.IDtcTransaction'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{0FB15084-AF41-11CE-BD2B-204C4F4F5020}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I've used the following code:

 

 

'Load template package


SSIS - Variables sharing from child to parent package

  

Hi,

I'm a having a problem in passing the value of a variable from child package to the parent package in SSIS.

Can anyone please help me??? Any process would be helpful..either using configurations or using scripting (c# or vb.net)..


Thanks,

Antiflag


Progametically creating xmlsource for ssis package.(Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapte

  

·         I am getting error while adding --> (Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter). It is expecting Assembly ..

Already i added Below dlls in my web application.

c: \Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Dts.Design.dll

Creating a SSIS package to get data from free DBF (Visual FoxPro table) in 64 bit SQL Server

  

Hi everybody,

I originally posted in the wrong forum MSDN Thread

I'm trying to create (for test purposes) a package to load data from VFP table into SQL Server 64 bit. So far I can not figure out how to do it - I created DSN as outlined in Todd's blog, but I don't know what to do next.

Can someone please give me step by step instructions so I will know if it's possible or not? I was under impression it's not possible, but Todd's blog seems to suggest otherwise.

Thanks in advance.


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Creating an Excel Spreadsheet programmatically using VB.NET

  
The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server.

Creating an Excel Spreadsheet and Adding Data to It Programmatically

  
The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged application can interrelate with Excel as a COM server.

Creating Reorderlist Programmatically

  

I hope to get some guidance on how to create reorder lists programmatically because of the situation described below. Thank you.

I have a page where I want to have a toDo Iist for each of several (typically 10) milestone categories. I can think of 3 options.

I don't know in advance how many 'active' categories there will be but I there is a known fixed total of about 20  categories, of which from 1 to 20 may be 'active'

1. Have one Master ReOrderList And Then Nest the Category ReorderLists Inside 

2. Create the reorderlists programmatically.

3. Brute force approach. Hardcode all 20 reorderlists and their associated sqldatasources, and then set visibility

depending on which category is active. This approach is the simplest but has the disadvantage of too much

repetitive code and the reorder lists would be in a fixed order, when I really want them to match the category order.

 

 

 


using SharePoint List for SSIS package

  
I need to use a sharepoint list as data source for SSIS package, it works fine until the sharepoint site implement Kerberos only (IIS 7) authentication, anyone knows how to get around this? thanks.

Erro While Creating a Solution Package With a timer Job

  

Hi All,

I am trying to create a test Timer job in SharePoint and want it to be deployed as a solution package. I am using a web application project and added Timerjob.cs, fetaurereceiver.cs, feature.xml and manifest.cml files in in. Then I created a .dff file to arrange the solution package. But when I tried to create a solution package with makecab.exe i got the below error

100.00% flushing current folder

I dont know what to do with that . Please if anyone can send me an example where a timer job was deployed as a solution package it will be of great help

Thanks,

 

 


Isha kapoor My SharePoint Blog Follow me

Programmatically creating a SharePoint Site based on user input

  

I am working on developing a “Create Website Wizard” for SharePoint 2007 that would do the following (I am simplifying a little for the sake of this post):

 

1.      Take user input as follows:

Title:

Description:

Product Code:

Category (I, II, III):

The category will be a choice field (radio buttons or a dropdown)

SSIS Package Transaction locks entire table

  
Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

Execute SSIS Package PART based on Some Condition

  
Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

SSIS package Scheduled Job Not Running

  
I'm not sure which way to research this issue.. I have an SSIS package that we can run just fine manually. But, when we put it into a scheduled job, it won't run. The package is on the same system as the SQL Server (2005), but it does call for updates from tables on another SQL Server (2000) on another domain (trusted). So, I'm thinking this might be a permissions / security issue, but not sure where to start... The package ProtectionLevel is "don't save sensitive", but that didn't help. Any help would be appreciated. Thanks. jill

How to make SSIS Package Fail when something goes wrong

  
Hello, Is there any way I can make the package (SSIS 2005) fail when something happen? Like: if some value become zero I will to make the package fail from with in the SSIS, so that the AgentJOB  that started this package will know? Thanks, Prabhat Nath
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend