SSIS Tips and Tricks

Recently I have got myself involved in developing Sql Server Integrate Services (SSIS) packages.  While I was doing it I had picked up a few tips and tricks that I would like to share here.

First trick is to pass parameters from Job Agent into SSIS.  It is possible to assign values to your package on Agent Job's configuration tool.  Details can be found at:

http://www.buildingmeaning.com/?p=171

For example, if you are a package variable called Subject, then you can set (override) this variable with the steps decribed in the link by adding the property path \Package.Variables[User::Subject].Properties[Value] with the desired value.

Second trick is to connect to your data source setup in your package from Script Task.  For example, if you have a data source called "My Data Source" setup in your SSIS, and you can use it in Script Task by doing:-

SqlConnection conn = Dts.Connections["My Data Source"].AcquireConnection(Dts.Transaction) as SqlConnection;


http://msdn.microsoft.com/en-us/library/ms136018.aspx


Third is actually a tip for preventing SQL injection by using parameterized sql

SqlCommand command = new SqlCommand(commandText, conn);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

The above specifics the ID is an integer type, so it can prevent values that is not a valid integer.

Hope you find them useful.