SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple Steps to Creating SSIS Package Configuration File


Simple Steps to Creating SSIS Package Configuration File

Author
Message
zeetechie
zeetechie
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 36
I'm currently using a SQL server table to store the SSIS configuration settings for my packages. I haven't played with the XML option, but find it interesting. Could someone comment on some of the practical applications of using the XML configuration file approach instead of using a SQL server table?

EDIT: and what I mean is what the advantages of using XML config files that could not be done or be done better vs. using the SQL server table approach, in real world application.

Thanks!
R_Casey
R_Casey
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 71
I have just completed DTS to SSIS conversion and the XML config file worked great for my application. It made deployment much easier that before.
rubinov
rubinov
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 15
I used to create packages as files on disk. And after development is completed I copy them to appropriate production directory.

When creating configuration file (CF below) the only option I see is to set package CF with particular path_name.

Is it possible to have package understand to work with CF located in the very same directory (along with package file) no matter what path was documented for CF?

Or is it possible to change configuration file path without rebuilding the package?

Thanks

Yuri Rubinov
Famson
Famson
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 230
You can place the package configuration file in any location. What is important is for you to select the appropriate configuration file during deployment and to ensure the file can be read.

Should the path of the configuration file be changed, then the package should be re-deployed using the configuration file in the new path.
David Betteridge
David Betteridge
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1173 Visits: 1822
I think I read some where that after adding a new environment variable that you have to reboot. Is that true?
Famson
Famson
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 230
David, if you create a new system environment variable, you may have to restart the system, but for a user environment variable, you may have to log off and log on again.

Cheers!
Mathew Michuta
Mathew Michuta
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 119
I personally LOVE using the XML format. One thing that I found cumbersome however, is viewing the XML. If you right click on the .dtsConfig file, choose to open it in Visual Studio Version selector, it gives you one looooong XML string.

Hold Ctrl, press K, then D, and VS auto formats the XML for you. This has saved countless hours of reformatting.

This small nicety makes a world of difference, and old developer friend of mine showed me that trick.
Misha_SQL
Misha_SQL
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1009
Thank you for the article and thank you to Mathew for the trick with Visual Studio shortcut. Very useful!



Lempster
Lempster
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3661 Visits: 1657
zanoni.harris (5/14/2009)
Could someone comment on some of the practical applications of using the XML configuration file approach instead of using a SQL server table?

...what I mean is what the advantages of using XML config files that could not be done or be done better vs. using the SQL server table approach, in real world application.


Like a previous poster I use an Environment Variable as the 'parent' package configuration which contains the details of the SQL Server database table in which the 'child' package configurations are stored.
The only advantage I can think of for the XML file approach is that it is more intuitive, i.e. you can see immediately what values you are setting for the variables/connection strings within your package rather than having to ascertain the location/name of table and then query that table.
The big disadvantage is security; by storing the configuration details in a table you can set very granular permissions to that table - important if the table holds connection strings to production environments.

Regards
Lempster
zeetechie
zeetechie
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 36
Great, thanks for the info Lempster. Also, great tip with the VS and viewing XML files Mathew.

Best,

Z
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search