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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 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
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1809 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-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 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
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

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



Lempster
Lempster
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 1659
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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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