SSIS and ODBC connections

Question

SSIS and ODBC connections

grahamc

SSCertifiable

Points: 6762
More actions
September 4, 2007 at 7:32 am

#118384

Good day all,
Here is situation -> We have a large number of development environments with a lot of on-going development. Most of these teams use DTS quite heavily and therefore will use SSIS in the same manner.
We have a very strict change control procedure when moving between DEV & TEST, TEST & UAT, UAT & LIVE.
Basically the problem I have is that a lot of our DTS/SSIS packages use multiple database connections. All these connections change between all of our environments. I was thinking along the lines of having generically ODBC connections on each SQL Server (IE. DataSource A, DataSource B, etc). This way if I can reference the ODBC, the connection is always the same, always exists and does not require me editing the SSIS package in order to release to the next environment.
I would hate to see what happens if I forget to change a connection string.
I have tried using DataReader Source in SSIS, but I keep getting the error message Cannot acquite a managed connection from the run-time connection manager when I setup my ODBC connection.
Any ideas on how to fix, or solutions for the above problem??
Thanks in advance,
Graham

Viewing 15 posts - 1 through 15 (of 15 total)

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

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

You should look into package configuration files in SSIS. We also use a configuration database to allow us to dynamically configure our SSIS packages simply by making a change to a SQL table.

grahamc SSCertifiable Points: 6762 More actions · Answer 2

Now that sounds quite interesting... How does the config database work? Can you setup connection strings for it? Something like this?

TYPE DEV1 DEV2 DEV3 ....

SourceA X Y Z

.....

Something along those lines??

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Package configurations are the way to go. Here is what we have done.

We use XML configuration files. We have created a directory on our local development machines C:\DTS\SSIS\Configurations (the DTS is because of legacy udl files) and there are some sub folders to keep things neat. The package configuration files are stored in these folders. Now we used the C drive because it is the operating system drive on every server my hardware group sets up - ensuring that every server and workstation has this drive (making it easier later).

You turn on package configurations, set the default XML file location in this directory, and then simply check off the server and database names in all of the connection strings (and the username and password for our legacy AS400 system). In development, the initial setup of everything is to our development environment. This is important because if a configuration file is not found it always defaults to the development environment (if you are going to accidentally update something, make sure it is not production).

Finally, the important step is to create the folders and copy the configuration files into them on the server that will EXECUTE the package (not the one that stores it). So if your packages are on server A and the job agent of server B is running the packages, server B needs the correct configuration files.

We use some template packages, so many of the packages we create use the same configuration files. This means that when we deploy it, it automatically has the appropriate configuration file on the production server. In cases where we cannot use the template packages, we create a new configuration file and deploy the configuration to the appropriate production, development, and QA servers.

It sounds like a lot of work, but it is pretty easy to manage.

grahamc SSCertifiable Points: 6762 More actions · Answer 4

Sorry I am a numpty when it comes to XML, what do the config files look like?

Sounds to be a good solution, and is what I want, and should be easy to manage once its all up and running.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

The config files look like any well-formed XML file - a bunch of tags indicating rows, columns, values, and relationships. XML Notepad (free from MS) opens and saves the configuration files without messing them up, or a find and replace with notepad will let you change the server names in them.

As far as a final deployment solution, this still feels a bit ugly to me. It is a move forward from the pile of udl files you would have had in DTS, but it still seems like one of the areas in SSIS that could use some polish. Unfortunately SQL 2008 did not improve this (unless they have hidden something really well). I would expect to have a deployment method similar to deploying a website in which you choose the destination and the configurations and it is all applied and moved to the appropriate SSIS server's MSDB database.

However, I will take a bit of deployment frustration along with all of the good things we got in SSIS any day.

grahamc SSCertifiable Points: 6762 More actions · Answer 6

Great!

I will have a look at that.

Only just recently started SSIS, but does look a lot more powerful!

grahamc SSCertifiable Points: 6762 More actions · Answer 7

I am seriously struggling with this... has someone got an example that they could email me?

grahamc SSCertifiable Points: 6762 More actions · Answer 8

Is this config file correct?

ConfiguredType="Property" Path="\Package.Variables[User::tSourceConnection].Properties[Value]" ValueType="String"

Data Source=dev001server040;Initial Catalog=db_iris;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

</ConfiguredValue>

</Configuration>

</DTSConfiguration>

All I am trying to do, is have the source and destination set by an external file.

Once the config file is correct, then how do I use it in the SSIS package?

grahamc SSCertifiable Points: 6762 More actions · Answer 9

<?xml version="1.0" encoding="utf-8"?>

<ConfiguredValue>Data Source=dev001server040;Initial Catalog=db_iris;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

Manish-485033 SSC Journeyman Points: 85 More actions · Answer 10

You don't need to do anything. When you create configuration file using wizard, just check checkbox which says enable package configurations. Becuase it knows the path where you have saved it, it will load these parameters first and then your package variables. Just try to change a value in your config file. Create a new database and run a script and create a same table in new database. Then change value from db_iris to new database name, save file and run package. Check now new database which has been populated by your package.

grahamc SSCertifiable Points: 6762 More actions · Answer 11

Sorry, but I have to be missing something now...

I have an SSIS package that takes a load of data from server1.db_1 (conneciton1) and then inserts that data (after manipulation) into server2.db_2 (conneciton2). In the current package it is setup as an OLE DB Source and an OLE DB Destination.

Now with using the config files how do I change each of these tasks so that I can still execute the SQL against connection1, do the manipulation and then insert into connection2? I feel like there is a step missing.....

Graham

Now I know why I stayed away from programming

Manish-485033 SSC Journeyman Points: 85 More actions · Answer 12

If you are going to use same source and destination then there is no need to use config files. Config file helps you change values at runtime. Assume the above source and destination are in your development machine and now you need to deploy these packages on your test server. So by changing values in config files you can change the value for server and database/catalog name for source and destination and the package will run fine. You don't need to go and change settings into your package. Values from config will override the settings of your package and will use new values.

Hope this helps.

Manish

grahamc SSCertifiable Points: 6762 More actions · Answer 13

Ok, I am starting to understand now.

From the example that I gave, what SSIS tasks do I use to fetch data from the connection1 and insert into connection2?

Just a straight "EXECUTE SQL TASK"??

Lisa Ngo Newbie Points: 3 More actions · Answer 14

I use the Script Task for more flexibility. Within the Script task you'll have to write vb.net code though and connect to the appropriate connection strings.

This also changes the way you define the connection Strings. It will be User Variables instead of the Connection Managers. These variables are also exportable to the XML Config file and can be loaded at runtime to different environment.

However, I bet you can probably use the EXECUTE SQL TASK too, but you might have to have one task to retrieve data, another TASK to and another EXECUTE SQL TASK to insert data.

I just went through a crash course (learning through books and online (googling)) on SSIS in the past 2 weeks trying to get my first two SSIS projects delivered.