I had a functioning DTS package (2000) migrated to SSIS (2005). It works fine on the server it originated on. I copied the dtsx file to the other server (another town, another network, no connectivity between SQL servers). It imports fine but won't run because the connection manager parameters are for the source server. You can manually change them for each execute of the package, but you can't save the new values.
So, within the project in BIDS, I enabled configurations and added two XML configurations. I then debug and build the deploy package. When importing on the new server, I can select the correct configuration file with the correct connection strings and values that go in them. But the import fails because it still grabs the original connection parameters to the source server. I've tried 30 or 40 different ways to get around this, including deleting all connections in the source DTS package in the "Project File". Nothing works.
I'm exasperated. It would be nice if I've missed something obvious. I've been following the "Deploying Packages" tutorial at http://msdn.microsoft.com/en-us/library/ms365338(v=SQL.100).aspx and all steps seem to work, right up to the last import.
Can anyone help?
asked May 06 '10 at 05:34 PM in Default
It sounds like it might be the protection level of the package, click on a blank space in the control flow and check the security settings.
If the package works ok in BIDS, click on a blank space in the control flow, then click on file|Save copy of 'Package Name' as, and fill in the relevent details, and change the package level configuration to 'Rely on Server storage and roles for access control'. This means that all sensitive data is saved to the server, but also that anyone with the required access can run the package.
Also, check to make sure that the connections are not dynamically set within the package. I quite often need to dynamically change the database, server etc of a connection and I do so via variables or a script task.