Passing parameters & variables into SSIS package when invoked from C# .Net

SSIS has 2 confusing concepts around dealing with variable. There were Variables earlier and now we can see Parameters also.
They are not really same and there are many places in internet talking about it. Below is a simple code snippet on how both can be set if we are executing SSIS package from C# application.

Sample setup

This sample uses a simple SSIS package which does calculation of square for all the number from 0 to N

• One parameter named 'number'. This will be used as loop's higher end
• One variable names 'counter'. Used as looping variable
• For Loop Container - This loop from 1 to number parameter. Initate FindSquareAndLog script block.
• FindSquareAndLog script block - This finds the square of variable counter and logs.

There is C# code which invokes this .dtsx file

FindSquareAndLog script block

This is part of .dtsx package. Editing the C# code in SSIS Script task in Visual Studio is simple as clicking on the 'Edit Script' button. As seen this is simple C# script which writes to Dts.Log

C# to pass parameter and variable values

This runs from a .Net application which invoke .dtsx package. Very straight forward. Every execution will create an ExecutionInstanceGUID internally. It is captured here. Very much useful if an operation involves series of SSIS package invocation and logs needs to be correlated.

Experiments

Lets try adding new variables and parameters before invoking from the .Net app.

---

private static void AddParametersIntoPackage(Package pkg)
{
    pkg.Parameters["number"].Value = 3;
    pkg.Variables["counter"].Value = 10;


    pkg.Parameters.Add("dfd", TypeCode.Boolean);
    pkg.Variables.Add("dfd", false, "", 3);
    pkg.Parameters.Add("df", TypeCode.Decimal);
}

---

Can see it just runs though there is no variables or parameters with these names defined in the dtsx pacakge. Try more so that we will end up in a conclusion that dtsx pacakges can be simply generated from our C# code

http://www.sqlchick.com/entries/2013/9/15/getting-started-with-parameters-variables-configurations-in.html
Happy coding

SSIS - Index of features

• Creating package
• Programmatic creation - https://msdn.microsoft.com/en-us/library/ms345167(v=sql.110).aspx
• Variables & Parameters
• Inbuilt System variables list -https://msdn.microsoft.com/en-us/library/ms141788(v=sql.105).aspx
• System::ExecutionInstanceGUID - The unique identified generated each time a SSIS package executed. This id can be used to correlate the operations and logs in SSIS package.
• Control flow
• If condition in package - No out of the box support. Add expression into Disabled property of task
• For loop http://www.bidn.com/blogs/TomLannen/bidn-blog/2640/ssis-for-loop-containers-part-1
• Script task
• Using variables and Parameters in C# script task.
• Event handling
• Event firing from C# script task
• Catching events in C# executor. 
• Running package
• Run the package and capture events using C# .Net.
• https://msdn.microsoft.com/en-us/library/ms136090(v=sql.105).aspx
• Run the package by passing parameter values.
• Run the package by modifying logging file path connection
• Run the package by injecting logging provider and connection manager
• https://msdn.microsoft.com/en-us/library/ms136023.aspx
• Logging
• Logging from C# script task
• https://msdn.microsoft.com/en-us/library/ms136131(v=sql.120).aspx
• Enable logging of script task log entries
• Logging into sysssislog table.
• Extending SSIS
• Custom tasks
• Developing custom task
• Deploying custom tasks into GAC.
• Developing custom UI for the task

Best practices in SSIS Logging

• Log always using SSIS logging mechanism
• Do not reinvent logging mechanism using any manual methods even in C# script tasks
• Script task to log into SSIS logging stream
• http://dataqueen.unlimitedviz.com/2013/09/how-to-enable-custom-logging-for-an-ssis-script-task/
• https://msdn.microsoft.com/en-us/library/ms136131%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
• Inject the log destination from invoking platform. This will help to correlate the SSIS logs with other application logs.
• eg: If the SSIS is invoked from C# application let the C# provide the logging connection string.
• Better log all the events and point to database as logging store.
• Have periodic routine to cleanup the logs.

---

Above are the best practices as of my understanding and experience with SSIS. Sorry to say that, I cannot guarantee that it will work for all.

Do we need SQL Server Integration Services running to execute SSIS / .dtsx package?

Question - Do we need a SSIS windows service to execute .dtsx file?

Here is the scenario. We have a .dtsx package file and need to execute it. Should we need to have SQL Server Integration Services running in our machine to execute that package?

Answer - No

Lets look at what is SQL Server Integration Services. By definition

"The SSIS service is a Windows service that monitors the SSIS packages that are running. The SSIS service also manages the storage of SSIS packages" 
From Microsoft KB https://support.microsoft.com/en-us/kb/942176 & MSDN Page

It clearly says we don't need need SSIS running in our machine to execute a package. We simply need the DTExec.exe utility to run .dtsx packages. So what is the use of SSIS windows service?

It is just to keep the .dtsx packages safe, initiate, monitor and stop the execution. It really means that there can be different users and roles who can only execute packages. Also helps to control the packages in remote machine.

Question - Do we need SQL Server running to run integration services package(.dtsx)

The SSIS is related with SQL Server. So do we need to have any SQL Server instance running, if we want to run a .dtsx package?

Answer - No

Though the Integration Services is for data transformation, it is not developed with tight integration with SQL Server. We can develop any algorithm using integration services. As seen above DTExec.exe is the execution utility. It doesn't need any SQL Server instance running to execute dtsx package is the package is not establishing connection with SQL Server.

What is the confusion?

Why people often confuse .dtsx package with SQL Server? The reason is mainly everybody thinks that since Integration Services are for ETL and data related operations, it must be linked with SQL Server and SSIS. Another reason is the expansion of SSIS starts with SQL Server. The better way is to start calling it as simply "Integration Services". As of my understanding even MSFT has the confusion. Some places they use simply Integration Services, some places Microsoft Integration Services and sometime refer full SQL Server Integration Services. So we cannot blame anybody for getting confused :)

So lets consider this as another technology to achieve workflow execution platform recommended for building enterprise-level data integration and data transformations solutions. This technology has many tools and components to help us in various phases of development and deployment. 

BIDS - Business Intelligence Development Studio

The tool which is used to create the workflow using drag and drop mechanism. It is Visual Studio itself. It gives option to define parameters, variables, connections, log provider and edit the script tasks. It captures the workflow steps in the form of xml and saved as simple file with extension .dtsx.

.dtsx

The file extension of file which contains the workflow in xml. This format is open and XSD can be found in MSDN. This file can be imported to SSIS package store to have better management. Integration Services .(dtsx) files can be created either from BIDS or using our own program. 

DTExec.exe

Utility used to to execute any .dtsx package either stored in file system or SSIS package storage. Parameters are passed via command line. Location

[Drive]:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

DTExecUI.exe

GUI utility to run .dtsx packages. Provides UI to enter the package parameters. Located at

[Drive]:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\DTExecUI.exe

SSIS

A windows service used to store and track execution of Integration Service packages (.dtsx)

Microsoft.SqlServer.ManagedDTS.dll

This is the .Net managed dll which provides API to create and execute .dtsx packages. This contains the namespace Microsoft.SqlServer.Dts.Runtime which holds the relevant classes. Located in below location if 2012 is installed

[Drive]:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

References

https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
http://stackoverflow.com/questions/454802/what-is-the-ssis-package-and-what-does-it-do

Not abel to create DTS.LogProviderTextFile.2 when running SSIS from .Net

Recently I was trying to execute a SSIS package from C# .Net code and it was not working as expected. So I decided to get the logs from the package. Unfortunately there was no logs. So I decided to inject the LogProvider and ConnectionManager from my C# code during execution.

Google lead me to a MSDN link which explains the same.
https://msdn.microsoft.com/en-us/library/ms136023(v=sql.105).aspx.

I selected SQL Server 2012 to get more accurate code snippet. The BIDS version I used was VS2010 based.

But I got below exception when I run the sample code.

A first chance exception of type 'Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException' occurred in Microsoft.SqlServer.ManagedDTS.dll

Additional information: The log provider type "DTS.LogProviderTextFile.2, {0A039101-ACC1-4E06-943F-279948323883}" specified for log provider "{652479A1-923C-452D-9328-5F703624B5FC}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.

Googled again and was not able to get much help. So decided to debug myself. I added LogProvider from BIDS UI and checked its type in Visual Studio before executing the package. It says the type is "DTS.LogProviderTextFile.3". It clearly indicate that if we create log provider with .3 suffix, it will work. I tried and it succeeded.

So if the target SSIS run time is SQL 2012 change the provider type to DTS.LogProviderTextFile.3.