Data Sources issue

[Joe H]

I have about 8 data sources all pointing to a DEV environment. I am ready
to change them all to point to Test environment - so I manually went into
the 8 and just pointed to different servers - all DB's are same name. No
matter what I try - it doesn't like the change. I opened SSMS and connected
using the same SQL authentication my DS are using - and I have correct
access and can insert/update tables from within SSMS.

I then created a new package - and used the same data source and did a
simple Execute SQL task count rows in a table and it works great. However
the same Execute SQL task in one of the existing packages does not work.
It's crazy...

[jhof...@googlemail.com]

Hi Joe,

I understand you are frustrated, but if you don't post any error
messages we really can't help you.

Good luck!
J

[Antoon]

It's probably the password that has to be reentered when you change the server.
Best way is to keep the connection properties: server, user, pasword in an
Configuration table, then when you change environments you simply change the
settings in the configuration table without having to deploy

[Todd C]

I agree with Antoon: Package Configurations are the way to go. But you can
take it up a level and VERY little to modify when you move a package to
Production:

1) In your DEV environment, create a DB called SSIS_Config.
2) In your SSIS Package, create an OLE DB connection to this Db
3) Right-click on the Control Flow, select Package Configurations.
4) Create an XML Configuration for the connection to the SSIS_Config Db.
Save the XML file to a simple path on your C:\ drive.
5) Create SQL Configuration entries for other DB connection Strings,
creating a new Configuration Filter for each.

Now on your Production Server:
6) Create (or backup and restore) the SSIS_Config Db from DEV
7) examine the entries in the table and change the connection string
settings as required so they point to production databases.
8) Copy the xml file from step 4 above to the SAME LOCATION on the server.
Edit this file so that it points to the Db created in Step 6.

Now, when you run a package in your design environment, it will go the to
DEV.SSIS_Config DB to get connection string to the databases it uses.

When you deploy that package to production and it runs, it will read the xml
file on the PROD Server, figure out that the SSIS_Config DB is on the PROD
server, and got THERE to get the connection strings to its Connection
Managers.

I know it seems a little involved, but it works great and after its setup,
you don't have to keep changing Connection Strings to deploy packages.
--
Todd C

[Joe H]

I will try the config way again - I spent much time trying to get it to work
at my last job.

The problem I'm having is I only have abotu 10 different data sources and I
use them in my connection managers area (I right click down there and select
"New connection from Data Source". So when I move between DEV and QA for
example - I only needed to change 10 Data Sources in the Solution Explorer.

What is killing me is even though I do this, and I check the servers both in
the Data Source and connection area and they both say my QA server - the DTS
packages when ran - still are trying to connect to my DEV sources. Putting
package level passwords and selecting "Encrypt ALL with password" help
much - but still get some packages trying to remember the old connections.

"Todd C" <To...@discussions.microsoft.com> wrote in message
news:41720E75-EF6E-4E28...@microsoft.com...

[Todd C]

Joe:
Selecting a ProtectionLevel of EncryptAllWithPassword won't do anything for
helping the Data Sources connect to the correct servers. All it will do is
keep people without the password from running or looking at your package.

If you are NOT using Configurations, try this:
Right-click on a package in SSMS and select Run Package.
Click on the Connection Managers page on the left, then examine the
connection string of each entry in the list. This will tell you what the
DESIGN of the package is.

Using Configurations, you can override these at run-time.
--
Todd C

[Joe H]

I have been saving the packages on my hard drive in design mode - should I
be saving on Server intead?

"Todd C" <To...@discussions.microsoft.com> wrote in message

news:550178AF-6617-44ED...@microsoft.com...

[Todd C]

Joe:
No, keep on saving your packages on the file system of your local machine.
BIDS usually sets up a folder structure under My Docuemnts\Visual Studio
2005\...

When you are ready to put a package into production, there are a number of
ways to do it. One is to use SSMS and connect to the SSIS instance, then
right-click under the Stored Packages|MSDB or File System node and select
Import Package. Another way is the package deployment utility in BIDS.

This is a departure from DTS 2000 design where DBA's typically made changes
to packages IN PRODUCTION. Microsoft have moved this to more of a
Development/Production type of environment.

HTH

--
Todd C

[Joe H]

What I am finding strange is some of my packages naturally have different
source to target data sources. I'm getting errors when it parses my XML
configuration file unless all the packages have the same connection manager
sources and targets - despite them not being used by some packages.

Example - if I don't have the Source1 and Source2 in both packages in the
connection managers - I get errors from the config:

Package1 pulls from Source1 and pushes to Target1

Package2 pulls from Source2 and pushes to Target1

"Todd C" <To...@discussions.microsoft.com> wrote in message

news:41720E75-EF6E-4E28...@microsoft.com...

[Todd C]

If you are using the same Config XML file foe several packages, each with
different Connection Managers, then yes, you will get errors like that.

In the methodololy I described, I create ONE configuratiion entry of EACH
Connection Manager. The first in the list is the XML file on the server that
tells the package where the SSIS_Config database is located. And THAT
database (and table) has one record for EACH database connection that might
ever be used in any package.

If the package only has 2 Connection Manager (besides the connection to the
Config DB) then it only needs and reads 2 rows from the table. The next
package might need those two, plus a third. It only grabs what it needs. It
ignores the rest of the rows because it is not designed to need them.

Since your package is getting ALL of its configurations from an XML file,
the number of elements stored in the config file MUST match the elements
(CM's, Variables, etc.) in any and all packages that reference it. If the
file has 3 Connection Managers configured and your package only has 2, you
get an error. If the file has 3 Connection Managers configured and the
package is looking for 4, then the 4th will just use whatever was specified
at design time.

Follow the methodology. I have used it and it works. Feel free to contact me
off-line for detailed instructions: tchittenden at wahlstrom d com.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]