Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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
0
earngreen
Asked:
earngreen
  • 7
  • 5
  • 2
  • +1
1 Solution
 
Robert SchuttSoftware EngineerCommented:
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

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

Here replace | with double quotes ("). Don't use single quote twice :D
0
 
Jared_SCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
earngreenAuthor Commented:
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.
0
 
earngreenAuthor Commented:
robert_schutt, Not sure what you mean could you post example.
0
 
Robert SchuttSoftware EngineerCommented:
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.
0
 
earngreenAuthor Commented:
robert_schutt, sorry this still has an error on my system.
0
 
Robert SchuttSoftware EngineerCommented:
did you check if the name is right with
DIR /X C:\

Open in new window

0
 
earngreenAuthor Commented:
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.
0
 
Robert SchuttSoftware EngineerCommented:
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?
0
 
earngreenAuthor Commented:
I resolved the @ symbol and removed it that was incorrect. Will run your command line to check?
0
 
Robert SchuttSoftware EngineerCommented:
Ok, this is how it looks on my system by the way:
capture
0
 
earngreenAuthor Commented:
This quite frankly has been the most idiotic thing that I have been through with microsoft but the answer is:

'c:\"mssql (x86)"\90\dts\binn\DTexec.exe'
0
 
TempDBACommented:
Did you even tried my suggestion above with ID 38406695?. It was infact the same thing.nfact the same thing.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27867810.html#a38406695
0
 
earngreenAuthor Commented:
After trying many things finally got it to work.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now