I am setting up my first SSIS package and have no problem debugging in BIDS, i.e. Package runs from end to end without any errors. However, when I schedule the job in the SQL Server Agent, it fails at what seems to be the login to the source server.
A section of the error text as below:-
Code: 0xC0202009 Source: Package1 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'tsmreports'.". End Error Error: 2010-09-09 13:52:01.38 Code: 0xC020801C
Source: Data Flow Task 1 Source - AccountPeriod  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with
error code 0xC0202009.
Whilst the package has the user id and password for the Source saved within it, do I need to set up a separate connection string in say a variable and execute this as part of the package? If so how do I do this?
View Complete Post