SSIS job connection tab problem

Question

SSIS job connection tab problem

Kathi Kellenberger

SSChampion

Points: 11811
More actions
July 5, 2007 at 2:19 pm

#177333

I have created several SSIS packages with BI studio using connections to the development system. I can save them to the package store on the dev system and set up jobs to run them. So far, so good.
Then, I save the packages to the production system package store, set up the jobs and modify the connections on the data sources tab so that they point to the correct production servers and locations. At this point, the packages fail because they can't connect to the servers. The only thing I have found to solve this problem is to modify the connections in BI studio, resave the packages in production. Then they work fine in the jobs since I use the connections as defined in the packages.
I have verified the connection strings, checked security, etc.
The two servers are running 64 bit SQL Server 2005 Enterprise Edition sp2.
This is driving me crazy, because I want to have identical packages running. I want to just modify the connection properties in the job depending on which server it is running on.
Any ideas?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Viewing 5 posts - 1 through 4 (of 4 total)

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

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 1

Just a thought - Are you encrypting the packages when you load them into SQL so the connections strings are stored correctly?

Kindest Regards,

Catherine Eibner
cybner.com.au

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

I'm not using any encryption. Today things are working properly. I am in the process of changing the service accounts, so maybe just restarting the services made a difference.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

We don't have our QA/Production systems yet, but we have setup our SSIS Packages to dynamically configure the connections using information stored in a Configuration Database table. The only fixed requirement in our setup is that the file that points to the database will have to be in the same directory on each server.

This should make moving our packages between systems fairly simple (or so we hope).

Anil-289324 Grasshopper Points: 15 More actions · Answer 4

There are 2 permanent ways to define connection string :

1) XML Config file (recommended)

2) SQL server table.

whenever; deploying the package on production change the connection file accordingly.

Also you can use one connection file for more than one package of same or different connection string.