I am using SSIS 2008 and have a Data Source (Solution Explorer) that originally was using DB1 on Server1 and its connection string. I created a Shared Data Source in my 5 packages using that Data Source.
I then changed the Data Sources Server to Server2 with a different DB (DB2) and its connection string. I did this by double clicking the Data Source under Data Sources in the Solution Explorer (Data Source designer) and pressing the Edit button. I made the changes then closed it.
I then checked the Shared Data Sources in all my packages and they all showed the new changes and were pointing now at Server2 with the correct DB2 in the connection string.
But when I ran the packages I found the data was being updated in the original database (DB1) on Server1. After much hand wringing I found that even though the Data Source Designer was showing the Server and connection string properly, which I right clicked on the Shared Data Source - it showed the old Server (Server1) and the old DB (DB1)
Does that mean I have to go into each Shared Data Source and change the Server and Connection string through the properties???
If that is the case, what is the point of having a Shared Data Source. At first I thought that maybe the Shared Data Source wasn't created as a Shared Data Source but then I realized if that was the case, the Data Source Designer would also be showing the original Server and DB when I change the Data Source.
Why is it working like this???
Thanks,
Tom
I then changed the Data Sources Server to Server2 with a different DB (DB2) and its connection string. I did this by double clicking the Data Source under Data Sources in the Solution Explorer (Data Source designer) and pressing the Edit button. I made the changes then closed it.
I then checked the Shared Data Sources in all my packages and they all showed the new changes and were pointing now at Server2 with the correct DB2 in the connection string.
But when I ran the packages I found the data was being updated in the original database (DB1) on Server1. After much hand wringing I found that even though the Data Source Designer was showing the Server and connection string properly, which I right clicked on the Shared Data Source - it showed the old Server (Server1) and the old DB (DB1)
Does that mean I have to go into each Shared Data Source and change the Server and Connection string through the properties???
If that is the case, what is the point of having a Shared Data Source. At first I thought that maybe the Shared Data Source wasn't created as a Shared Data Source but then I realized if that was the case, the Data Source Designer would also be showing the original Server and DB when I change the Data Source.
Why is it working like this???
Thanks,
Tom