Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Simple Steps to Creating SSIS Package Configuration File Expand / Collapse
Author
Message
Posted Thursday, May 14, 2009 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 6:43 PM
Points: 4, 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!
Post #717288
Posted Thursday, May 14, 2009 2:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 1, 2013 2:47 PM
Points: 15, Visits: 67
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.
Post #717372
Posted Friday, May 15, 2009 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 8, 2013 6:20 AM
Points: 1, 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

Post #718304
Posted Friday, May 15, 2009 3:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:49 PM
Points: 47, Visits: 179
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.
Post #718326
Posted Friday, May 15, 2009 4:56 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:20 PM
Points: 941, Visits: 1,755
I think I read some where that after adding a new environment variable that you have to reboot. Is that true?
Post #718383
Posted Saturday, May 16, 2009 11:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:49 PM
Points: 47, Visits: 179
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!
Post #718546
Posted Saturday, May 16, 2009 2:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:03 PM
Points: 30, 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.
Post #718554
Posted Sunday, May 17, 2009 4:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:02 PM
Points: 536, Visits: 767
Thank you for the article and thank you to Mathew for the trick with Visual Studio shortcut. Very useful!


Post #718751
Posted Monday, May 18, 2009 8:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 2,119, Visits: 1,446
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
Post #719086
Posted Monday, May 18, 2009 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 6:43 PM
Points: 4, Visits: 36
Great, thanks for the info Lempster. Also, great tip with the VS and viewing XML files Mathew.

Best,

Z
Post #719134
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse