Simple Steps to Creating SSIS Package Configuration File

Question

Simple Steps to Creating SSIS Package Configuration File

Viewing 11 posts - 16 through 25 (of 25 total)

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

Famson SSC Eights! Points: 997 More actions · Answer 1

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 Old Hand Points: 330 More actions · Answer 2

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 SSCertifiable Points: 5407 More actions · Answer 3

Thank you for the article and thank you to Mathew for the trick with Visual Studio shortcut. Very useful!

Lempster SSCoach Points: 15651 More actions · Answer 4

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 SSC Veteran Points: 222 More actions · Answer 5

Great, thanks for the info Lempster. Also, great tip with the VS and viewing XML files Mathew.

Best,

Z

Famson SSC Eights! Points: 997 More actions · Answer 6

Nice input Lempster, storing configuration settings in a table makes it possible to set very granular permissions. But configuration files in XML format can also be secured using Access Control List (ACL) on the operating system.

Although this does not provide the granular permission setting as available in a table, however, it secures the xml configuration file and protects it from unauthorized access.

Cheers!

Lempster SSCoach Points: 15651 More actions · Answer 7

True, but permissions to tables should be under exclusive control of DBAs whereas ACL could be changed by IT Ops/Domain Admins etc.

Famson SSC Eights! Points: 997 More actions · Answer 8

That's true, I assumed the database server is under the control of the DBA and any change is subject to a proper change control process.

But I sure agree with you that in an environment where the Systems Admins and other Operations staff can effect changes at anytime, then using a table seem perfect. 🙂

hennie7863 SSCommitted Points: 1653 More actions · Answer 9

In my projects sofar i've have created configuration files in my packages. I made one development folder and a production folder. In this case i only have to pass the production folder to the next machine in the DTAP process (in theory). And, i thought that MS advised to do this like this. Build in a development environment deploy somewhere else. Even Jamie Thompson best practices says you should put the configuration in the projectfolder (so i did).

Now i've some interesting (in either case for me). the package configuration file is 'connected' in the development environement in the package. When i want to deploy it to the production folder i do a built (bin) and copy it to production folder. But what do i do with the configuration file?

Currently, i point my configurationfile to the production folder but that, i would say, is not very neat. building the package in a development folder, pinpointing to production folder and having a production folder. Am i thinking wrong or what?

Or should i integrate the development folder in the production (project) folder?

Greetz,

Hennie

vani_r14 SSCrazy Points: 2843 More actions · Answer 10

Hi there

I think for a novice like me this is very useful, however in the part when adding the file to configuration a comment would be quite helpful (if there is no config file already you can create one here or to that extent)...

Cheers

AdityaSingh SSC Journeyman Points: 89 More actions · Answer 11

Same thing happening to me.

after following all the steps correctly.

when i want to change the connection file values.

they are changed, and package runs successfully but not with the changed value.

But with old settings.