asked on

Execute SSIS package from Stored Procedure

I am trying to execute a stored procedure from SSIS and having a probleme with the path. The space between the mssql and (x86) is the culprit. I have attempted to format differently but with no success. Can someone tell me how to format the following to make this work?

SELECT @Path = 'c:\mssql (x86)\90\dts\binn\DTexec.exe'
SELECT @PackageName = '\package.dtsx'
SELECT @SQLServer = 'srv001'
SELECT @DB = 'db1'
SELECT @StartDate = '''' + '2012/09/06'+ ''',''' + 'yyyy/mm/dd'+ ''''
SELECT @EndDate = '''' + '2012/09/07'+ ''',''' + 'yyyy/mm/dd'+ ''''

SELECT @Cmd = '"' + @Path + '"' + ' /SQ ' + '"' + @PackageName + '"' + ' /SER srv01'
+ ' /SET ' + '"' + '\Package.Variables[user::@StartDate].Properties[Value]' + '"' + ';' + '"' + @StartDate + '"'
+ ' /SET ' + '"' + '\Package.Variables[User::EndDate].Properties[Value]' + '"' + ';' + '"' + @EndDate + '"'

EXEC @ReturnCode = xp_cmdshell @Cmd

Robert Schutt

You could try 2 workarounds for the space in the path:
1) put the path in your global PATH variable so you can leave it out in the call
2) use the DOS 8.3 name for the directory, it's probably "MSSQL(~1", you can check that in a cmd prompt with:

DIR /X C:\

Open in new window

TempDBA

SELECT @Path = '|c:\mssql (x86)\90\dts\binn\DTexec.exe|'

Here replace | with double quotes ("). Don't use single quote twice :D

Jared_S

Have you tried to execute this without the full path to dtexec?

SELECT @Path = 'DTexec'
SELECT @PackageName = 'package.dtsx'
SELECT @SQLServer = 'srv001' 
SELECT @DB = 'db1'
SELECT @StartDate = '''' + '2012/09/06'+ ''',''' + 'yyyy/mm/dd'+ ''''
SELECT @EndDate = '''' + '2012/09/07'+ ''',''' + 'yyyy/mm/dd'+ ''''

SELECT @Cmd =  @Path + ' /SQ ' + @PackageName + ' /SER srv01'
+ ' /SET ' + '\Package.Variables[user::@StartDate].Properties[Value]' +  ';'  + '"' +  @StartDate + '"'
+ ' /SET ' + '\Package.Variables[User::EndDate].Properties[Value]' + ';'  + '"' + @EndDate + '"'

--print @cmd

EXEC @ReturnCode = xp_cmdshell @Cmd

Open in new window

and a hat-tip to http://www.codeproject.com/Articles/27156/How-to-Call-SSIS-Package-from-the-Stored-Procedure

earngreen

ASKER

jared_s, that works but the problem is that it runs under default 64 bit and I need to run 32 bit. If I run 64 bit there are more errors.

earngreen

ASKER

robert_schutt, Not sure what you mean could you post example.

Robert Schutt

On my system, if I create a directory "C:\MSSQL (x86)" I can access that through the 'old' name "C:\MSSQL(~1" so you could try changing your first line to:

SELECT @Path = 'c:\MSSQL(~1\90\dts\binn\DTexec.exe'

Open in new window

Then you shouldn't need quotes around it.

earngreen

ASKER

robert_schutt, sorry this still has an error on my system.

Robert Schutt

did you check if the name is right with

DIR /X C:\

Open in new window

earngreen

ASKER

I am certain that the directory is correct. When I run the results of my original query from SQL or from an SQL job it works perfectly. Just not sure why I am having trouble with this syntax.

Robert Schutt

What I posted so far was purely based on your remark "The space between the mssql and (x86) is the culprit". The notation with ~ is an alternative for the same directory without the space. But on your system the exact name may be different, that's why I suggested checking it with "DIR /X", not to suggest that you don't know where the program is installed ;-)

One other thing that seems odd to me (but I don't know much about the workings of that exe) is the different syntax (specifically the @) between '\Package.Variables[user::@StartDate].Properties[Value]' and'\Package.Variables[User::EndDate].Properties[Value]', could it be a copy/paste error?

earngreen

ASKER

I resolved the @ symbol and removed it that was incorrect. Will run your command line to check?

Robert Schutt

Ok, this is how it looks on my system by the way: