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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Use AdventureWorks CREATE Table dbo.Transactions (TranNo int, Quantity int, Amt money) CREATE TABLE [dbo].[ErrorLog]( [id] [int] IDENTITY(1,1) NOT NULL, [msg] [varchar](max) NULL, [dte] [datetime] NULL, [src] [varchar](50) NULL ) ON [PRIMARY] INSERT INTO dbo.Transactions Values (1,5,30), (2,7,55), (3,10,75) EXECUTE xp_cmdshell 'md c:\files' --create folder on server for packages EXECUTE xp_cmdshell 'md c:\ssis' --export records from table into tab delimited text file EXECUTE xp_cmdshell 'bcp databasename.dbo.Transactions out c:\files\Transactions_20101231.txt -c -t"\t" -S ServerName -T' EXECUTE xp_cmdshell 'bcp databasename.dbo.Transactions out c:\files\Transactions_20101231.txt -c -t"\t" -S ServerName -T' |
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]
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
CREATE PROCEDURE [dbo].[usp_ImportTransactions] @DefaultDate DATE = NULL AS BEGIN TRY --DECLARE @DefaultDate DATE DECLARE @SSIS VARCHAR(8000) -- create a table variable to record the package execution results DECLARE @Output TABLE ( ExecutionResults VARCHAR(MAX) ) DECLARE @retVal INT --set deault date to either date passed or yesterday SELECT @DefaultDate = COALESCE(@DefaultDate, DATEADD(dd, -1, GETDATE())) -- build the DTEXEC command and also pass a value to the package variable SELECT @SSIS = 'DTEXEC.exe /f "c:\SSIS\ImportTransactions.dtsx" /set \package.variables[User::Filename].Value;\"Transactions_' + CONVERT(VARCHAR(8), @DefaultDate, 112) + '.txt\"' --------------import the file------------------------ -- USE "INSERT EXEC" to execute the package but also capture the execution results INSERT @Output ( ExecutionResults ) EXEC @retVal = xp_cmdshell @SSIS -- if the returned value is not zero then there has been an error -- so we need to save the execution results IF @retval <> 0 BEGIN INSERT ErrorLog ( msg, dte, src ) SELECT ExecutionResults, GETDATE(), 'ImportTransactions.dtsx' FROM @Output -- we then need to raise an error to alert some action RAISERROR ( 'SSIS package %s failed -see ErrorLog table rc = %d', 16, - 1, 'ImportTransactions.dtsx', @retval ) RETURN END END TRY BEGIN CATCH --rethrow the error to the calling application -- the code for this has been taken from books on-line (2008) EXEC usp_RethrowError END CATCH GO |
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.
|
1 2 3 4 |
SELECT * FROM errorLog WHERE src = 'ImportTransactions.dtsx' ORDER BY id DESC |
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
|
1 |
'DTEXEC.exe /f "c:\SSIS\ImportTransactions.dtsx" /set \package.variables[User::Filename].Value;\"Transactions_20101123.txt\" /set \package.variables[User::ImportFolder].Value;\"c:\files\\\"' |
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.
Load comments