locked
Error when loading SSIS package RRS feed

  • Question

  • Hi all,

    I have problem with SSIS. When I open my packages, I always get an error message which says like this:
    There were errors while the package was being loaded.
    The package might be corrupted.
    See the Error List for details.

    And, in error list, I get this error message:
    Error loading DIM_BUSINESS_TYPES.dtsx: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'user'.".

    But the package itself seems doesnt have any errors at all. When I try to run the package, it runs fine.
    Btw, I use package configuration which is stored in sql server table, including the password for connectionstring.

    So, what's actually wrong with my package?

    Thanks

    Thursday, December 6, 2007 5:13 AM

Answers

  •  Andy Leonard wrote:

       Although it may exist, I am unaware of a method of disabling this behavior. I've tried setting the DelayValidation property for the Connection Managers and the Package and SSIS continues to test the connections.

     

    You can use the Work Offline option to prevent SSIS from testing the connections (SSIS menu...Work Offline).

    Friday, December 7, 2007 4:17 AM

All replies

  • Try setting Protection level to Dont save sensitive

     

    Thursday, December 6, 2007 6:06 AM
  •  

    Helpful KB article which gives additional details

     

    http://support.microsoft.com/kb/918760

    Thursday, December 6, 2007 8:49 AM
  • Hi Ricky,

     

       Some validation takes place when SSIS opens a package in the designer. One of the checks is a test of each Connection Manager. If the connection test experiences an issue (invalid username / password combination, for example) you will see an error similar to the error you decsribe. The most likely reason the package executes without issue is the package configuration sets the proper username and password for the connection.

     

       Although it may exist, I am unaware of a method of disabling this behavior. I've tried setting the DelayValidation property for the Connection Managers and the Package and SSIS continues to test the connections.

     

      

    Hope this helps,

    Andy

    Thursday, December 6, 2007 11:31 AM
  •  Andy Leonard wrote:

       Although it may exist, I am unaware of a method of disabling this behavior. I've tried setting the DelayValidation property for the Connection Managers and the Package and SSIS continues to test the connections.

     

    You can use the Work Offline option to prevent SSIS from testing the connections (SSIS menu...Work Offline).

    Friday, December 7, 2007 4:17 AM
  •  

    I cannot see this option of "SSIS-->Work Offline" in my IDE? Does anyone know what is the problem?
    Friday, May 16, 2008 10:31 AM
  • The work offline option is not available unless you are editing the package in the context of a project. If you just right-clicked the package file, and chose Edit, the package is opened directly, without a SSIS project being created.

     

    To create the project, open BIDS, choose File..New Project, and pick the Integration Services project template. To add you package to the project, right click the project node in the Solution Explorer window, and choose Add Existing Package. This will create a copy of the package you select, under the context of the project.

     

    Friday, May 16, 2008 12:52 PM
  • I have an issue that is pretty closely related to this one. The problem seems to be a bug in the sequence of events fired during package execution by SSIS. SSIS fires off the connection manager events which try and connect to data sources, even prior to loading other settings including passing a valid connection string through other means.   The question is why does SSIS do this for even OLEDB data sources, knowing well that passwords are not being stored inside the package?

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6679cb1f-1210-417e-89ea-53d840000b10
    Tuesday, December 8, 2009 2:47 PM
  • This has been plaguing me as well. I think I have found a solution to what I would consider a bug. At least this may send you down the right path.

    In my case I have a SQL Configuration file that is currently only holding the connection string value. I was getting the same error regarding an invalid connection string as above. I went in to the configuration table and manually edited the connectionstring value, closed then reopened the package and/or solution. 

    So, to solve the error from popping up when you open the package - First copy the valid connection string from the configuration table or from the property in the SSIS package. Then change the value manually in the table to anything, I did this: 

    update SSIS_Configurations
    set Configuredvalue = 'this is it!!!'
    where ConfigurationFilter = 'tblAdmit_Upd_Insert'

    I then opened my package/.dtsx file and it had an additional error about an invalid connectionstring, this is before I set the connection string back to something valid. Odd thing was when I went to look at the connectionstring value in the configuration file through the BIDS GUI it showed the old value of the valid connectionstring even though it complained of the string being invalid, which it actually now was as the real value was simply 'this is it!!!' <- hardly a valid connectionstring. Now close the package and solution. Rerun the sql to update the configuration table back to it's valid connectionstring value. Re-open the package and the error should be gone.

    Wednesday, May 23, 2012 11:46 PM
  • Have you tried the DelayValidation on all of the Data Flow tasks to True?

    Raul Villaronga, Sr. Applications Development Specialist Penn National Insurance

    Tuesday, August 21, 2012 3:04 PM