x

Deploying SSIS Package Using BIDS

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?

more ▼

asked May 06, 2010 at 05:34 PM in Default

BrianLegg gravatar image

BrianLegg
2 3 3 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

Good luck!

more ▼

answered May 06, 2010 at 09:50 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 13 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939
x27

asked: May 06, 2010 at 05:34 PM

Seen: 2308 times

Last Updated: May 06, 2010 at 05:34 PM