Execute SSIS Package from a Stored Procedure in SQL Server 2005

TheUndecider
TheUndecider used Ask the Experts™
on
Hello,

I've been reading and trying to figure out the best way to accomplish this, but so far I haven't been able to do so.

In SQL Server 2000, we have a stored procedure that executes a DTS package.  This DTS package imports data from an Excel spreadsheet to a table. This stored procedure uses a proxy account and sp_cmdshell like this:

-- Run the DTS package using the proxy account
EXEC master..xp_cmdshell 'dtsrun /sMyServer /UMyProxyAccount /PMyProxyAccountPassword /NMyDTSPackage', NO_OUTPUT

That is working perfectly in SQL Server 2000.  However, we're in the process of migrating to SQL Server 2005.  I've created a SSIS package in SQL Server 2005 that is supposed to import the same data to the 2005 table.  I've saved the SSIS in the SQL Server.  Nevertheless, I don't know the best way to call this SSIS package from a 2005 stored procedure. One issue is that other staff members will have to execute this stored procedure and they do not have administrative permissions.  I'm hoping the solution would be as simple as in 2000, but I am not sure.


Any ideas on how to accomplish this?

Thanks!

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
>>I'm hoping the solution would be as simple as in 2000, but I am not sure.<<
It could be no different than DTS.  You use the SSIS command line dtexec Utility:
http://msdn.microsoft.com/en-us/library/ms162810.aspx

Author

Commented:
Hi AC,

I tried what you suggested, but I am getting this error:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started:  1:28:41 PM
Progress: 2010-02-01 13:28:41.91
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Error: 2010-02-01 13:28:42.12
   Code: 0xC0202009
   Source: ExcelRecordsImport Connection manager "SourceConnectionExcel"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
End Error
Error: 2010-02-01 13:28:42.12
   Code: 0xC020801C
   Source: Data Flow Task Source - 'Clinical Sample$' [1]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2010-02-01 13:28:42.12
   Code: 0xC0047017
   Source: Data Flow Task DTS.Pipeline
   Description: component "Source - 'RecordsSample$'" (1) failed validation and returned error code 0xC020801C.
End Error
Progress: 2010-02-01 13:28:42.12
   Source: Data Flow Task
   Validating: 33% complete
End Progress
Error: 2010-02-01 13:28:42.12
   Code: 0xC004700C
   Source: Data Flow Task DTS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2010-02-01 13:28:42.12
   Code: 0xC0024107
   Source: Data Flow Task
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  1:28:41 PM
Finished: 1:28:42 PM
Elapsed:  0.968 seconds
NULL


If I execute the package from the Integration Services screen, it works.  

Apparently it's failing validation.  


Some extra notes regarding this task:

I am trying to execute this line:

xp_cmdshell 'dtexec /sq ExcelRecordsImport /ser MyServer '

I creatded the Excel file, so I am the owner.  
I am also an Domain Administrator.

I've created a Proxy Account that it's under the Proxies > SSIS Package Execution folder.  I'd like to use this account to run all my SSIS packages.  

Thanks for your help.
Top Expert 2012
Commented:
First the parameters are case sensitive.  It should be /SQ and /Ser
Secondly, try running this utility without xp_cmdshell on the same box as where you normally run SSIS.  If all is well then run it again, but this time on the same SQL Server box, provided that it is a 32-bit version of SQL Server.  If it is 64-bit than you are SOL.

Author

Commented:
Sorry for not coming here sooner.  One of my problems was that I was trying to use the 64 bit version of SQL when I needed the 32 one.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial