Error when calling SSIS Package from Stored Procedure

[Theadmans]

I have saved an SSIS Package using the File System option as C:\Packages\MyPackage.dtsx.

I can run this package succesfully from the XP Command Prompt by typing:-

dtexec /De mypassword /F C:\Packages\MyPackage.dtsx

I then tried creating a stored procedure (using Microsoft SQL Server Management Studio Express) in the Database I am updating which runs the following command:-

EXEC xp_cmdshell 'dtexec /De mypassword /F C:\Packages\MyPackage.dtsx'

When I run the Stored Procedure I get the error "Unable to load the package as XML because of package does not have a valid XML format."

I am trying to convert a Visual FoxPro table into SQL Server 2005 table. Can anyone help please.

 

[AlexCuse]

I didn't think that SQL Express supported SSIS?

That could be your problem, could also be that your package is stored on your PC's file system where the server can't see it.

FWIW, I think its' better to create a job to run the package then use sp_start_job to run it from t-sql, rather than using xp_cmdshell.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Theadmans]

Thanks for the reply Alex.

I come from a FoxPro background so am a novice when it comes to SQL. If I use your suggested approach am I still saving the Pacakge as a file or SQL Server package?

Also can you give me a brief overview of how to use Sp_start_job to run the package - many thanks.

 

[AlexCuse]

I always deploy my packages to the server, so I can't really help you there.

the syntax for sp_start_job is just

exec sp_start_job N'my job name'

I believe, but Books online should help (SQL Server help files)

If you're using Sql Express I don't think know if you'll be able to run these packages on the server at all though.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page