Post reply

SSIS data source vs configuration file

  • March 10, 2010 at 9:52 am

    #145931

    I have the following situation on a SSIS 2008 package:

    Package: MyPackage.dtsx

    Data Source:MyConnection(Server A, Database A)

    Package Configuration File:MyConfigFile(Server A, Database A)

    Initially, the connection information in the Package Configuration File (Server A, Database A) is the same as the connection information in the Data Source (Server A, Database A). When I change the connection information in the package configuration file (Server A to Server B, Database A to Database B) I expect the new connection to take effect when I open the package on BIDS. That does not happen. Instead I get a 'Synchronize Connection Strings' dialog box that shows the updated connection (Server B, Database B) as 'Old Connection' and the original connection (Server A, Database A) as the 'New Connection'. Basically, the connection information in the Package Data Source overwrites the connection information in the Package Configuration file.

    I looked through different forums and the most common advice is that this is a 'bug' that happens when you use data sources so stick with Connection Managers only.

    I've loaded the package on SSIS agent job and the connection information of the job reflects the connection information in the SSIS Package Configuration File. So, I think this only happens when you open the package in BIDS.

    Why does this happen? Is there any way to stop it? Is there any documentation from Microsoft about it? (I couldn't find one).

  • March 11, 2010 at 6:55 am

    #1131989

    Here you can find some documentation on data sources:

    http://msdn.microsoft.com/en-us/library/cc671619.aspx

    There is also a section about the disadvantages of using them.

    So indeed, the advice is not to use them, but use connection managers instead.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 12, 2010 at 10:11 am

    #1132847

    Thanks. I was looking for something like this.

Viewing 3 posts - 1 through 2 (of 2 total)

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