Making it easier to move between environments

Question

Making it easier to move between environments

Viewing 8 posts - 16 through 22 (of 22 total)

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

prarav SSC Rookie Points: 42 More actions · Answer 1

I read somewhere (dont remember that it was BOL or some other blog) that XML Config has one issue that it wants that all the CONNECTIONS that are in XML Config File should always exists in the PKGs that are using the same XML Config File...

I dont want to create connections in my PKGs that I am not using.

Here is my situation.. and this is why I had to use Variables in Pkg to point to connection in XML file...

Let me know if there is any other way...

Lets say I have a PKG1 that is using database A and B

And I have an other PKG2 that is using Database A and C

Now in my Single XML Config file I had 3 DB connections A B and C

Now when I open the pkg1 in SSIS it gives me error message saying you dont have connection for "C"

and when I open the pkg2 it gives me error saying you dont have connection for database "B"

To avoid all the error messages I defined the varibles in PKgs and those variable in XML config file points to DB connection...

This way I dont get ERROR but Warnings.....

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 2

Pravar YOu are absolutely right!!!!! If u are using XML file and reuse same file for other packages then u have to have same connections for all other packages . It gave me mulitple errors. I have a package Ahistory that uses 2 different OLEDB connection, however all this connections are not needed. what i thought was use same dtsconfig file for others packages that have 2 or 3 connections , but not all.

It gave me a error. I belive it requires to have all connections in the package that XML file is refering too.

Even when i tried delayvaldation set true for all my connections, when i opened the package it gave me all kinds of errors.

Now , i belive i need to find way to use variables for my connections.

ANy help is very helpful. Thanks

prarav SSC Rookie Points: 42 More actions · Answer 3

I am using Variables and it works fine...

All you need to do is define Variable at pkg level.... say db1_connection

and on the Connection Manager for that connection use expression

It should read something like this once you set it up

@[user::db1_connection ]

Now when you create your XML config file just select Expression and Value....

You are all set...

Just change the value to point to where ever you want to point...

Hope this Helps..

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 4

Thanks Pravar!!! What i did was something little different. I have like 50+ packages. All together there are 6 databases that i will be connected to. SO for eack database i Made a seperate XML config file . So now i have 6 config file. So any packges (OLEDB COnnections only) that uses connections to particular database, i added that config file in that package to reuse the exsisting config file.

Here let me explain........

Package A uses to OLEDB conn, say, Con1 and Con2

so My packge A uses 2 config file made for eack conn, Con1 and Con2

Then my packge b uses 2 connections, say Con1 and Con3

so this packge will use 2 config file, one exsting Config file for Con1 and Another new config File with Con3...

and

so on......

Now anothet problem i am facing is for my bulk Insert Connections...

Like 40 + of packags either uses flat file soure or flat file destination. I am facing a challenge of making these flat file and file connections dynamic?

Do u suggets me some sites or articles to go through?

Thanks, KUmar

prarav SSC Rookie Points: 42 More actions · Answer 5

Well.. The answer is same Variables....

I have defined the path of my packages in a variable...

My packages also need FLAT File loads and while development the path can be a local drive but once it is done it will be a directory on Network... and that again can change in future...

So I created a variable "pkg_path" and one for "File_path"

And on connection manager use the variable to point to location....

The variable should ready value from your XML....

So all you need to do is change value in XML.... and you are done!!!

Make sense??

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 6

I understood this part. The problem, or i would say my confusion is , ok, i have 30 bulk insert connections, and every bulk insert connectiosn uses different files, however this files will be on D drive in different folders. SO no i have 40 variables, one for eack package. Now how many config file do i need ? If i made One XML config file, then choose my XML properties to file path, i have to go and edit the config file to varibales. So does that same variables ( one for each package will be picked up at run time) ?

I am total confuse here? DO u know any articles or some bolg or sites to read more abut it?

thansk in advance, kumar

prarav SSC Rookie Points: 42 More actions · Answer 7

No I dont know any web site or any article...

We did it by trying again and again...

To answer your question... I dont think you need multiple files for multiple connection...

I just use one XML file that has all my DB connection... It has all my File Path... It has all my Pkg path and more....

But every connection in pkg uses variable....

Now if I have to change anything all I need is make change in ONE XML File

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 8

Thanks Prarav!!!! Aprreciate ur time and response. let me try here . U r right, u don't learn until u make mistakes. i will update the progress. Once agian thanks.