Error while running SSIS Package (0x80040E4D)

  • ard5karthick

    SSCommitted

    Points: 1603

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    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

  • ard5karthick

    SSCommitted

    Points: 1603

    Yes...

    and accessing that table through SQL Server authentication...

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    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

  • ard5karthick

    SSCommitted

    Points: 1603

    Hi,

    The password is storing correctly...

    The Package is running successfully but its still giving the error message...

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    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

  • ard5karthick

    SSCommitted

    Points: 1603

    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.

    "

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    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

  • ard5karthick

    SSCommitted

    Points: 1603

    The Table in where the configurations were done from

    SSIS ---> Package Confiruration

    Thanks...

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    ard5karthick (6/19/2012)


    The Table in where the configurations were done from

    SSIS ---> 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

  • ard5karthick

    SSCommitted

    Points: 1603

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    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

  • rhythmk

    SSCertifiable

    Points: 7162

    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
    🙂

  • ard5karthick

    SSCommitted

    Points: 1603

    Hi Koen,

    We are using Package Configuration to store the details in the Configuration table...

    Thanks,

    Karthick.

  • ard5karthick

    SSCommitted

    Points: 1603

    Hi bhuvneshk,

    II run the package using BIDs...

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply