xp_cmdshell with DTExec

[Keith G Hicks]

I'm having a heck of a time getting this to work. I have an SSIS package for
importing data from a text file into a table in my database. If I right
click the package and select "run" it works fine every time. And it doesn't
matter if I save it as a file system or MSDB package. Either way, it works
as expected.

I need to be able to run this from a button on a application so here's what
I'm trying to do in SSMS:

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sql BaseviewImport /ser mySSISServer
/u sa /p mysapassword'
select @returncode

Here are my results:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 8:53:18 AM
Could not load package "BaseviewImport" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code
0x80004005 (Login timeout expired). The SQL statement that was issued has
failed.
Source:
Started: 8:53:18 AM
Finished: 8:53:33 AM
Elapsed: 15.75 seconds
NULL

Return code is 5.

I've tried moving the package between file system and msdb. That makes no
difference. I tried setting the roles of the package to public and that
didn't work either. Same results as above. Can anyone tell me what needs to
be done to make this work?

Thanks,

Keith

 

[Keith G Hicks]

OOPS! I goofed. This is not the newsgroup this was supposed to go in. Ignore
it here. I'm putting it in microsoft.public.sqlserver.programming where it
should be. If you can answer it, answer it there please. Sorry about that.

Keith

 

[paige]

I'm having a heck of a time getting this to work. I have an SSIS package
for
importing data from a text file into a table in my database. If I right
click the package and select "run" it works fine every time. And it
doesn't
matter if I save it as a file system or MSDB package. Either way, it works
as expected.

I need to be able to run this from a button on a application so here's
what
I'm trying to do in SSMS:

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sql BaseviewImport /ser
mySSISServer
/u sa /p mysapassword'
select @returncode

Here are my results:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 8:53:18 AM
Could not load package "BaseviewImport" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error
code
0x80004005 (Login timeout expired). The SQL statement that was issued has
failed.
Source:
Started: 8:53:18 AM
Finished: 8:53:33 AM
Elapsed: 15.75 seconds
NULL

Return code is 5.

I've tried moving the package between file system and msdb. That makes no
difference. I tried setting the roles of the package to public and that
didn't work either. Same results as above. Can anyone tell me what needs
to be done to make this work?

Thanks,

Keith