asked
SQL 2008 SSIS package, after importing from DTS2000, does not run when called from SQL Server Agent job step
Hi, 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..
The SSIS is not recognizing the password for the database from the DTS package. This is a security "feature". Since the packages are imported and converted you can change them by adding a configuration file to the package and placing your connection properties in there. This is assuming that I understood your post correctly.
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.
14 People are following this question.
