Soe Tun

my attempt to solve common tech problems

  Home  |   Contact  |   Syndication    |   Login
  14 Posts | 0 Stories | 213 Comments | 0 Trackbacks

News

Twitter







Archives

Post Categories

Image Galleries

Hi everybody,

It has been a long time since my previous post about mapping stored procedure parameters to SSIS package variables post.
I have been busy+lazy and never get around to posting a follow-up post until now.

In this post, I will show how to execute a SSIS package using the dtexec Utility from a Stored Procedure because it is one of the most frequent questions I received for my previous blog post.

First of all, I have a few important things to let you know.
I am a Senior .NET Developer and Database Administration is not my area of expertise.

Therefore, questions about Database Server configuration and permissions etc, I won't be able to answer all of them.
It will be faster to get the answer if you ask your Database Administrator if something is not working due to Server permissions and settings.

Here is the Stored Procedure signature I will use.

CREATE PROCEDURE [dbo].[pr_SSISDriver]
	-- This can be a network address like this ===> '\\serverName\d$\MySsis\mySsis.dtsx'
	@ssisPkgFilePath varchar(500),

	-- You can store any kind of configuration values in the SSIS Package DTS Config file.
	-- This can be a network address like this ===> '\\serverName\d$\MySsis\myconfig.dtsConfig'
	@ssisPkgConfigFilePath varchar(500),

	-- You can pass in currently logged-in User Id (if any) as a VarChar string in case you need to do something with it
	@currentUser varchar(200),

	-- My SSIS package dumps out an Excel file.
	-- This can be a network address like this ===> '\\serverName\d$\MySsisOutputFolder\'
	@outputFolderLocation varchar(500),

	-- My SSIS package sends out an email notification after successfully processing everything.
	-- You can just use the DTS Configuration file to store this (or) you can pass this in as a SSIS Package Variable.
	@smtpServer varchar(100),
	@sendEmailFrom varchar(100),

	-- pass in email addresses comma-delimitted like this ===> 'a@blah.com, b@blah.com'
	@sendSuccessEmailTo varchar(200),
	@sendErrorEmailTo varchar(200),

	-- pass in Date input fields as a string ===> 'MM/dd/yyyy'
	@mySampleInputDate varchar(10)
AS
BEGIN
	-- ================================================================================
	-- (1) Validate Input Variable values
	--     Perform the rest of the input paramater validations on your own
	-- ================================================================================
	IF nullif(ltrim(rtrim(@ssisPkgFilePath)), '') is null
	begin
	--{
		RAISERROR( 'Invalid ''ssisPkgFilePath'' value' /*Message*/, 16 /*Severity*/, 1 /*State (arbitary number)*/ )
	--}
	end

	-- ================================================================================
	-- (2) REMOVE trailing semi-colons from email address
	-- ================================================================================
	WHILE @sendErrorEmailTo like '%;'
	begin
		set @sendErrorEmailTo = LEFT(@sendErrorEmailTo, LEN(@sendErrorEmailTo)-1)
	end

	-- ================================================================================
	-- (3) Build the string to execute the SSIS package.
	-- ================================================================================
	DECLARE @dtsExecCmd varchar(4000) -- varchar(8000) is the max length allowed for "varchar"

	SET @dtsExecCmd = 'dtexec /F "' + ltrim(rtrim(@ssisPkgFilePath)) + '"'
	SET @dtsExecCmd = @dtsExecCmd + ' /CONFIGFILE "' + @ssisPkgConfigFilePath + '"' -- SSIS package config file for Database Connection
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::outputFolderLocation].Properties[Value]";"\"'        + @outputFolderLocation               + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::currentUser].Properties[Value]";"\"'                 + @currentUser                        + '\""'

	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::smtpServer].Properties[Value]";"\"'                  + @smtpServer                         + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::sendEmailFrom].Properties[Value]";"\"'               + @sendEmailFrom                      + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::sendSuccessEmailTo].Properties[Value]";"\"'          + @sendSuccessEmailTo                 + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::sendErrorEmailTo].Properties[Value]";"\"'            + @sendErrorEmailTo                   + '\""'

	-- Pass in the Date field as a VARCHAR string data type
	-- the corresponding SSIS package variable ****should be**** getting the proper Date value
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::myInputDate].Properties[Value]";"\"'                 + @mySampleInputDate                  + '\""'

	PRINT 'Print DTEXEC Command for debugging purposes: ' + @dtsExecCmd + '
'


	-- ================================================================================
	-- (4) Run the SSIS package by building the DTSEXEC command line
	-- ================================================================================
	-- Variable to hold the RETURN code from the DTEXEC utility
	DECLARE @result INT

	-- Declare TEMP table variable to hold the output messages from the SSIS package
	DECLARE @output TABLE( [ssisOutput] varchar(max) )
	INSERT INTO @output
	--{
		EXEC @result = master..xp_cmdshell @dtsExecCmd
	--}

	PRINT 'DTEXEC Execute Result = ' + cast(@result as varchar(10))


	-- ================================================================================
	-- (5) Check if any error occured
	-- ================================================================================
	IF (@result is not null and @result<>0) OR EXISTS(select 1 from @output where ssisoutput like '%error%')
	begin
	--{
		PRINT 'SSIS package ''' + @ssisPkgFilePath + ''' failed.'

		-- ================================================================================
		-- (6) Return Error Rows so we know there were errors while executing the SSIS pkg
		-- ================================================================================
		SELECT * FROM @output

		-- ================================================================================
		-- (7) Perform error logging as needed (if you want to).
		-- ================================================================================
	--}
	end


	SET NOCOUNT OFF;

	-- RETURN code from the DTEXEC utility
	RETURN @result;
END

Okay, the SQL Stored Procedure code is pretty self-explanatory.
The only elaboration I have to make is the section #5.

Basically, the dtexec command line utility prints out the SSIS package execution messages to the Console.
Each line gets returned into the stored procedure as "rows".
I am filtering those "rows" by looking for anywhere in the line with "error" string so I only get error statements.

You are probably going to ask how do I detect errors from my Client Application that executes this Stored Procedure.
That is what the section #6 is for.
You just check whether the stored procedure returned a row or not.
If it returns a row, that means there was an error while executing the stored procedure.

You might ask, well the SSIS has its own error-handling features. Why can't you just use it?
The answer is simple. You might need to display that there was an error while processing the user's request in your Web/Windows application.
The only way to know there was an error is to check if the stored procedure returned a result set.

I am not an expert at SSIS and this is my solution of running a SSIS package from a Stored Procedure.
There might be better solutions out there, but this solution works out well for me.

I hope this post is really helpful to many people out there.

Cheers,
Soe

posted on Wednesday, February 24, 2010 6:25 PM

Feedback

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 4/16/2010 5:50 PM Hector herrera
Soe,

Your script is awesome!!!

Thanks!

Hector

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 4/28/2010 5:29 AM Shehab
Same here, saved me lot of time (And trouble :D)
GREAAAAAAAAAAAT

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 5/11/2010 6:27 PM guest
The part of the script which returns the results of execution was great. We were just returning the error code and not the complete log of execution. Your script helped us debug the problems we were facing. GREAT work!!

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 9/30/2010 10:26 AM Sven
Didnt work for me. Dont know why but the @result variable is not storing the dtexec result code but just the rowcount of the resultset. Any ideas? Also I have to say that when the dtsx executes with no errors I also get several rows with the execution progress details.

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 9/30/2010 3:41 PM Soe tun
Sven, run this SQL command.

PRINT @dtsExecCmd


Copy and paste the output and run it from your Command Prompt (cmd.exe).
Then you will see if you are doing something wrong or not.

Other than that, whatever I have blogged here has worked for me.
So I am not sure what I can do to help you in your particular case.

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 10/26/2010 10:04 AM Carlos
hey, excelent work, it worked for me. I was wondering if there was a way to obtain a variable value after executing the ssis package?

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 9/14/2011 12:32 AM Viral
Its giving me an error :
" The file name "\\ServerName\C$\Temp\File10.DAT" specified in the connection was not valid."
when i tried to give a networkpath for my flat file.
It works when i stored my Flat file on the same location where my Sql server resides.

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 10/7/2011 3:38 PM Clark
As a stored procuedure what program are you running this in, and does it become an exacutable?

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 11/21/2011 4:41 PM Gordo
Thanks for this, I found it very useful. For people wondering how to configure access to xp_cmdshell without making app users server admins, you want to create a proxy account like this:

/*But there's a little bit more to it. Below is an outline of what need to be done. Step 2 and 3 are only needed if the one who is to execute xp_cmdshell isn't sysadmin. Note that the steps don't have to be performed in the order listed below.
1.We need to allow usage of xp_cmdshell in general (on 2005). Use "Surface Area Configuration" or sp_configure for this.
2.We need to have a user in the master database which has execute permission on xp_cmdshell. If you are uncertain about the difference between logins and users, you should read up about it in BOL.
3.We need to say what Windows account should be used when a non-sysadmin user is executing xp_cmdshell.

So, here's the TSQL script that does all above:
*/

--1, allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO



--2, grant permission to xp_cmdshell
USE master
CREATE LOGIN cmdshellProxy WITH PASSWORD = 'proxy!@#$%'

--Note, we are in the master database!!!
CREATE USER cmdshellProxy FROM LOGIN cmdshellProxy

--Run as login x
EXECUTE AS login = 'cmdshellProxy'
--Below fails, no execute permission on xp_cmdshell
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

--Note, we are in the master database!!!
GRANT EXECUTE ON xp_cmdshell TO cmdshellProxy

--Try again
EXECUTE AS login = 'cmdshellProxy'
--Execution of xp_cmdshell is allowed.
--But I haven't configured the proxy account...
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO



--3, specify the proxy account for non-syadmins (create a windows account called SQLCmdshellProxy, you may need to be logged in locally to successfully run sp_xp_cmdshell_proxy_account)
--Replace obvious parts!
EXEC sp_xp_cmdshell_proxy_account 'B1RS01\SQLCmdshellProxy','Proxy!@#$%'
EXECUTE AS login = 'cmdshellProxy'
--Execution of xp_cmdshell is allowed.
--And executes successfully!!!
EXEC xp_cmdshell 'DIR C:\*.*';
REVERT

--You have to grant impersonate to the low level sql server logins that must be allowed:
GRANT IMPERSONATE ON Login::cmdshellProxy TO MyAppAccount

go


--Cleanup (if you have to undo the above)
EXEC sp_xp_cmdshell_proxy_account null

DROP USER cmdshellProxy
DROP LOGIN cmdshellProxy
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE


# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 3/2/2012 5:21 AM Ashwini MG
Your script is really good helped us lot

# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 1/23/2013 8:14 AM Charles Mulupi
Am trying to execute this but it fails
exec Generate_TrialBalance 'D:\SCD\SCRIPTS\FOSA\FOSARPT\CombinedTBPKG.dtsx','D:\SCD\SCRIPTS\FOSA\FOSARPT\CombinedTBConfig.dtsConfig','SELECT dbo.Chart_Of_Accounts.GLAccount, dbo.Chart_Of_Accounts.ACCName, dbo.BTRANSACTIONS.Debit + dbo.BTRANSACTIONS.Credit AS Debit, dbo.BTRANSACTIONS.Transdate, 0 AS Credit FROM dbo.Chart_Of_Accounts INNER JOIN dbo.BTRANSACTIONS ON dbo.Chart_Of_Accounts.GLAccount = dbo.BTRANSACTIONS.GLDR WHERE dbo.Btransactions.Transdate>=1/1/2006 and dbo.Btransactions.Transdate<=10/8/2006 and (left( dbo.Chart_Of_Accounts.GLAccount,3)=B-3 OR LEFT( dbo.Chart_Of_Accounts.GLAccount,3)=B-4)'


with this error: Argument "" + CombinedTBPKG.Variables[User::BOSAIEDr].Properties[Value];"SELECT" for option "set" is not valid.
where could i be wrong.



# re: Execute SSIS Package from Stored Procedure with Parameters using DTEXEC Utility 10/17/2013 9:46 AM shouryasony
create procedure package
( @para1 dt,@para2 dt,@para3 dt,@para4 output)
AS
declare @ssisstr varchar()
declare @packagename varchar()
declare @servername varchar()
declare @params varchar()


set @packagename='----------'
set @servername='----------'
set @params='/set \Package.Variables[path].Value];"\"'+@para+'\""'
set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
set @ssisstr = @ssisstr + @params

DECLARE @returncode varchar()
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode

Post A Comment
Title:
Name:
Email:
Comment:
Verification: