June 10, 2012 at 1:56 am
Hi All,
I am facing an odd problem,
I have an SSIS Package that runs import and export jobs, They were created in a different server and were moved to two different servers. In one of the servers its running fine but in another server it is giving me the following error.
"Error loading dtsImportLoadCapacity.dtsx: 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 'QAUser'."."
I have a configuration manager that stores all the sensitive details in a Table and fetches it from the table while run time and the package is "password protected".
The package is running successfully but i am getting the error also.
I have to move this package to another server too, I want to know whether it will be a problem if i move it to another server.
I have some three days to figure this out.
So kindly help me in solving this problem.
Thanks
June 11, 2012 at 12:18 am
So you are using package configurations to store the sensitive information inside a SQL Server config table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2012 at 2:24 am
Yes...
and accessing that table through SQL Server authentication...
June 11, 2012 at 2:29 am
Check the config table if the password is stored correctly.
If you generate the data using the wizard in BIDS, it stores the password as ***** instead of the actual password.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2012 at 10:33 am
Hi,
The password is storing correctly...
The Package is running successfully but its still giving the error message...
June 11, 2012 at 1:18 pm
ard5karthick (6/11/2012)
Hi,The password is storing correctly...
The Package is running successfully but its still giving the error message...
Do you get any validation warnings? Any configuration warnings? (you need to log them, they won't show up in the Agent jobs history log.)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 15, 2012 at 7:36 am
Hi Koen,
I am sorry for taking so long to reply to your mail.
Yes i am getting a warning message if i run the Job in BIDS.
The message goes as follows:
"Warning loading dtsImportLoadCapacity.dtsx: Failed to load at least one of the configuration entries for the package. Check configuration entries for "dtsImportLoadCapacity" and previous warnings to see descriptions of which configuration failed.
"
June 15, 2012 at 1:54 pm
ard5karthick (6/15/2012)
Hi Koen,I am sorry for taking so long to reply to your mail.
Yes i am getting a warning message if i run the Job in BIDS.
The message goes as follows:
"Warning loading dtsImportLoadCapacity.dtsx: Failed to load at least one of the configuration entries for the package. Check configuration entries for "dtsImportLoadCapacity" and previous warnings to see descriptions of which configuration failed.
"
Well, it seems the configuration is failing. How does the package know where to find the config table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 19, 2012 at 4:10 am
The Table in where the configurations were done from
SSIS ---> Package Confiruration
Thanks...
June 19, 2012 at 5:02 am
ard5karthick (6/19/2012)
The Table in where the configurations were done fromSSIS ---> Package Confiruration
Thanks...
I know where the menu is to configure package configurations.
My question was how does the SSIS runtime engine know where the SQL Server config table is located? How does it know which server and database to go to?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 21, 2012 at 1:12 am
Hi,
We have created another connection manager which will have the server details of the Configuration table.
The Configuration Table is placed in msdb system database and SQL Server authentication is used..
Thanks,
karthick
June 21, 2012 at 1:59 pm
ard5karthick (6/21/2012)
Hi,We have created another connection manager which will have the server details of the Configuration table.
The Configuration Table is placed in msdb system database and SQL Server authentication is used..
Thanks,
karthick
OK. How is this connection manager configured? Is it hardcoded or do you use a package configuration?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 22, 2012 at 6:20 am
How are you vaildating that package is executing successfully ?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 23, 2012 at 11:58 pm
Hi Koen,
We are using Package Configuration to store the details in the Configuration table...
Thanks,
Karthick.
June 23, 2012 at 11:59 pm
Hi bhuvneshk,
II run the package using BIDs...
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply