Executing SSIS Packages continued

Comments 0

Share to social media

Following on from Nigel Rivett‘s article on three different approaches for running SSIS packages  I thought I would share with you some techniques which Nigel showed me while I worked along side him on a recent contract.  Any plaudits for this article should be directed to Nigel as these techniques were developed by him.

SSIS packages can deployed to run within SQL Server (the msdb) database or as stand alone files which can be executed.  The problem with the first approach is there is too much dependency on the DBA to install the packages into msdb and keep track of version control.  By far the best approach is to execute the packages from files using the DTEXEC command.  The downside is you will need a file depository area either on the SQL Server box or on an (application) server that SQL Server can access to load and run the packages.  The upside is as a developer you can deploy and test new versions by just copying the new version of the package (*.dtsx) to the file repository which can take seconds rather than days waiting for the DBA to get off his bottom.   The package will always run in SQL Server memory space (unless you have installed Integration Services as a separate instance independently – and if you have there will be a licensing issue).

I want to demonstrate to you the syntax to execute the package in code, (a stored procedure) and additionally pass parameters to a package variable.  I also want to show you the code you will need to capture any errors from package failures.

Assumptions:-

  • You need execute rights to  master..xp_cmdshell
  • SQL Server 2008 version
  • You have read/write access to c:\ on server

Run this code

You will need to copy the attached package “ImportTransactions.dtsx” to the c:\ssis folder. This package has two variables Filename and ImportFolder.

The expression for the ConnectionString for the “connection to flat file” is @[User::ImportFolder]+ @[User::Filename]

1190-clip_image002.jpg

 So if this package was to run it would try to import a file called “c:\files\Transactions_20091231.txt” (which doesn’t exist – the one we have created with bcp out is called “c:\files\Transactions_20101231.txt”).

We now need to create a stored procedure to run the package for any file or perhaps for a file dated the previous day as the default behaviour.  We also want to capture any errors if the package fails for whatever reason.  Here is the code.

You can run the stored procedure to use the default date or for a specific date (usp_ImportTransactions ‘20101231’) and maybe alter the text file and put in some text so that the package fails on a data conversion error to see how the stored proc behaves.

Conclusions:

The first  thing to note is that unless we explicitly capture the output and raise an error (which the example stored proc does) from executing the package when it fails then a) you won’t be informed of the failure and b) you won’t know what caused the failure.

To find out what happened after a failure occurred you can run.

The second thing to note is the rather perverse syntax for passing values to the package variables.

If you wanted to pass values to more than one variable in the package then you just carry on adding /set parameters and the string would look something like this

Once again I would like to thank Nigel Rivett for showing me these techniques and once you start using them I think you will find that deploying and running packages is a lot easier.

The sample integration services package can be downloaded below.

About the author

Patrick Index

See Profile

I have been contracting for 14 years and before that I worked for a software house for 5 years in the late 80's. I started out as a Lotus 1-2-3 developer and then did database development with Paradox. That soon became Access development which progressed to Access with SQL Server in a client server scenario and now I suppose I would describe myself as a SQL Server developer.

Patrick Index's contributions