Send Variable from C# to SSIS Package

[DuaneHaworth]

Hi All,

I am a newbie regarding DTS and SSIS. I inherited an application and I would like to make it more solid. So far I have managed to convert a DTS package to SSIS, update connection managers, and execute from an external C# application with dynamic datasource names.

The source file is a flat file with two fields. The destination is a SQL Server table with three fields. The third field contains an ID which gets filled in later(legacy code - this is what I want to change).

However, I know the ID going in and I would love to be able to send the ID from my C# application to the SSIS package and have it insert the ID into the ID Field on the database.

Is this possible?

Thanks, in advance.
Duane

 

[DuaneHaworth]

Nevermind. I figured it out.

 

[mrdenny]

Please post your solution so that it's available for future users to reference.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[DuaneHaworth]

1) In Control Flow tab, create a variable. (Right-click in workspace and select Variables)
2) Enter your variable name
3) And a Derived Column from the toolbox.
4) Edit the Derived Column and enter your desired name (ex: FileVersionID) for the column. Keep <add as new column> as the Derived Column and for the Expression, enter @FileVersionID (keeping with my sample names)
5) Link the source to the Derived Column
6) Link the Derived Column to the Destination
7) Now, when you edit the Destination, you will see your new column as an input type.
8) The code from C# looks like this:
ssisPackage.Variables["FileVersionID"].Value = fileVersionsStruct.FileVersionID;