Hi All, We are facing issues in SSIS package execution through SQL Agent Jobs. We are using Execute Package Tasks and all these tasks are throwing errors below for Authentication Failed. Errors: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. 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 'Domain\Username'.". I tried to browse through google and msdn, but its quite confusing. http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/43c84491-66f8-4a61-bd97-54a824d56be7/ - The above thread says to set DelayValidation = True for all the Tasks in the child package, but DelayValidation = False for child package itself. - The errors which we are getting are at the Execute Package Task level. (i.e. if we have Execute package task (named "Call C1", then the above errors we are getting for the SourceConnectionDB OLEDB Connection Manager in the C1 package). Any help is much appreciated !!! Let me know if you need more information. Thanks, Swapnil

How are you setting the Connection manger user names and passwords, i suggest use .dtsconfing files to set the user name and passwordsAbhinav

Hi, Can you execute package without using SQL Agent Job? I mean directly SSIS package?

Hi Abhinav, We are using dtsconfig to set the connection Manager properties. The Authentication is Windows Integrated security.

Ok, then how is your SQL Agent started in services.msc by default it will make the packages run on behalf of that same user, and by the looks of it the that user wouldnt have permissions on the SQL DB.Abhinav

Ok, then how is your SQL Agent started in services.msc by default it will make the packages run on behalf of that same user, and by the looks of it the that user wouldnt have permissions on the SQL DB. +1 Give the SQL Server Agent account permission to access the SQL Server instances, or run the job through a proxy.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi All, In the new environment, there is a different user and it has permissions to the DBs. The job is executed not through SQL Agent, but Control-M (a job scheduling tool). The job is impersonated through the user which has access to the underlying DBs. Even data is getting loaded into the database. But the log table shows error. Let me know if you need more information.

Even data is getting loaded into the database. But the log table shows error. Let me know if you need more information. What does that mean? you are able to load data but there are erors if errors what are the errors it can be the business rules, and you started the thread with the Windows JOB, now cntrl-M Please specify the errors more clearly in what all environments you have. i thnk the Windows job schduler error can be resolved by the things above, coming to cntrl-M specify the user impersonation and also if the same user imperosnation is present then give it admin rights then execute.Abhinav

Hi All, I have identified the issue, but dont know how to resolve. In the Child package, the Connection Manager SourceConnectionDB is pointing to the DEV Database. <DTS:Property DTS:Name="DelayValidation">-1</DTS:Property> <DTS:Property DTS:Name="ObjectName">SourceConnectionDB</DTS:Property> <DTS:Property DTS:Name="DTSID">{D160542E-D30B-401F-A53E-4336CCD99FDB}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager> <DTS:Property DTS:Name="Retain">0</DTS:Property> <DTS:Property DTS:Name="ConnectionString">Data Source=SQLPB93\DEV;Initial Catalog=SB_DEV;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Document_Cache_Refresh-{D160542E-D30B-401F-A53E-4336CCD99FDB}SourceConnectionDB;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager> <DTS:Configuration> The QAS and PROD user doesnot have access to the DEV DB. There is a configuration file (dtsconfig) created, which is indirectly used through the Environment variable to set this ConnectionString. And actually it does, data is getting read and loaded, but the log file shows error. When I manually changed the code: <DTS:Property DTS:Name="ConnectionString">Data Source=SQLPB93\QAS;Initial Catalog=SB_QAS;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Document_Cache_Refresh-{D160542E-D30B-401F-A53E-4336CCD99FDB}SourceConnectionDB;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager> <DTS:Configuration> The package gets successfully executed and the log doesn't show errors. I tried to set the DelayValidation = True for all the tasks, connection manager and the package itself. But nothing works, the same error if the code has DEV. Kindly let me know if you need more information. Thanks, Swapnil

I am not clear on what you meant still, though by the looks of it the data source is setting as incorrectly to dev which you wnat to be set as QA, so what you need to do is that find the path of .dtsconfig, as you said its set via the environment variable, and is passing the data source so you would need to change it to point to the DB you want to Abhinav

Hi Abhinav, We are using dtsconfig to set this particular ConnectionString. And actually it is working because the data is read from the SourceConnectionDB and loaded into the destination. The problem is that we have a records in SSIS LOG saying that Authentication failed for the QA User. If we manually changed the dtsx (View Code) as specified above, the LOG is not having any error record for authentication failed. It means that somewhere the design time ConnectionString is not getting overloaded by the new ConnectionString from the dtsconfig. We tried with DelayValidation=TRUE, but still the same issue. Thanks, Swapnil

Swapnil, What you can do is create a new .dtsconfig file and pass the appropriate datasource to it. I think you are using the environment variables to set the path of config file so set it there. If it s a parent child configuration then the config path or data source would be passed via the parent chid varibles setting check the setting there. i mean check this link for parent child varible configuration and change your package setting automatically http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/84f6808b-4772-4066-9f17-03fea708d1ebAbhinav

Hi Abhinav, Please refer to the below link. http://connect.microsoft.com/SQLServer/feedback/details/591936/delayvalidation-property-has-no-effect-for-sql-log-provider-and-no-ui-to-set-delayvalidation-for-logprovider We are stuckup here. Any ideas to resolve this issue? Thanks, Swapnil

Hi All, I think, this is a bug of SSIS Log Provider while using the Execute Package Task. The log provider doesnot get validated since the validation of the connection string is done after the validation of the Log Provider. To oversome this we need to use (UseParentSettings) for the child package tasks and it works fine !!! https://connect.microsoft.com/SQLServer/feedback/details/591936/delayvalidation-property-has-no-effect-for-sql-log-provider-and-no-ui-to-set-delayvalidation-for-logprovider# Cheers. Swapnil