SSIS from 200 machine to 2005 machine

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS from 200 machine to 2005 machine

Author

Topic

junkjar
Starting Member

4 Posts

Posted - 2009-03-18 : 07:59:31

Hi I have an Import SSIS package on my sql2005 machine that imports data from an old sql 2000 machine.

It works fine when executed from the wizard on the 2005 machine or from Integration services

However when I call it from within a stored procedure like this

Declare @SSIS_string5 varchar(200)
Select @SSIS_string5 = 'dtexec /sq "Export GG Groups " /ser XXX083'
exec master.dbo.xp_cmdshell @SSIS_string5

I get the following error ..

Error: 2009-03-18 11:31:42.62
Code: 0xC0202009
Source: Sql_2005_Format_Copy_Tables_From_Sac063 Connection manager "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
End Error
Error: 2009-03-18 11:31:42.64
Code: 0xC020801C
Source: Data Flow Task Source - bull_result_nmr_2005 [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this wit
h more information on why the AcquireConnection method call failed.
End Error
Error: 2009-03-18 11:31:42.64
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Source - bull_result_nmr_2005" (1) failed validation and returned error code 0xC020801C.
End Error
Progress: 2009-03-18 11:31:42.64
Source: Data Flow Task
Validating: 16% complete
End Progress
Error: 2009-03-18 11:31:42.64
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2009-03-18 11:31:42.64
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:31:40 AM
Finished: 11:31:42 AM
Elapsed: 2.343 seconds
NULL

(72 row(s) affected)

What sort of permissions does an SSIS need from within a stored procedure/ or agent job?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-18 : 08:58:41

The SQL Agent account will need access to the SQL 2000 box and any directories the task looks at/writes to. 'NT AUTHORITY\ANONYMOUS LOGON' does not have any writes to do this. My guess is that the SQL Agent is set to the local system account, change it to a domain account with the right access levels and it will work.

junkjar
Starting Member

4 Posts

Posted - 2009-03-19 : 05:07:50

Ok thanks for the help .. i'll see does that work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources