An SQL 2008 SSIS package, after importing from DTS2000, does not run when called from SQL Server Agent job step

0
votes

I have migrated a 4 DTS Packages from SQL2000 to SQL 2008 Using the Migration Wizard. of these 4. Package 1 Calls other 3 packages in its steps. I edited the package 1 and replaces the 2000 DTS executions to package executions and changed the connection manager settings( SQL Server Authentication). However, I am not able to execute this package from a SQL Agent Job. I get error

Code: 0xC0202009 Source: Package1 Connection manager "xxxx" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'xxxx'.".

xxxx is the SQL server login authentication as in connection Manager.

P.S.: I have tried many a options from mnay blogs and sites but I am still stuck..

1
sql-serverssismigration

1 Answers

1
votes

I have tried all the options available on different forums so far. But what actually solved my issue was: I changed the connection string to use Windows Authentication instead of SQL and it worked.