Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Changing .ini file to Global Variables

Status
Not open for further replies.

Catadmin

Programmer
Oct 26, 2001
3,097
US
I don't know how many people use .ini files with their DTS packages to set global variables and connection info, but we do it a lot where I work, so I thought I'd pass along how we changed this process when converting to SSIS.

Run the DTS Conversion Wizard to create your package as a .dtsx file (or which ever file type you want). Note, if you do the .dtsx file, you *must* add it to a project/solution in the Business Intelligence Developer Studio before you can use the Package Configurations options.

Disable the Script Task which looks for the .ini file. Go up to the menu and choose SSIS -> Variables. This will populate the variables list which will be pinned to your left (unless you've changed where this docks). Make sure the Control Flow tab is chosen, then right-click in the free space. Navigate to Package Configurations. This will open up a Wizard.

Pass the Wizard's first screen and, on the second screen, choose where you want your config file saved. The PCon Wizard generates an XML configuration file which actually replaces your .ini file. The third screen gives you the properties to export to the XML config file. Choose the items you want in the XML file (if you just randomly click all, it will ALL be in the XML file and you don't really need everything). Mind you, what you need in your config file varies depending on what you're doing, but you should at least choose Name & Value from all the variables plus from your connection info.

Once you've selected what you want in the config file, it shows you what you've selected in a final "info" screen and you just click Finish. Note that if you edit the config file at a future date, it will overwrite your current file, so backup the file someplace if you ever want to go edit it and then decide the changes you made were Very Bad... @=)

Finish updating the rest of your package as appropriate. Then just make sure your config file gets placed on the server with the new SSIS package and you should be set.

I hope this is helpful. If you spot errors in anything I stated, please let me know ASAP. I'm learning this all myself as I go, and we're still in the Development phase of our conversion.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
 
Further notes on the above, the XML Config file gets loaded into the package at the time the package is saved, not when the package is executed. If you switch servers after this point, the package will still be attempting to use the original XML config file at the original saving point. If you save the config file on your local PC (say C:\My Documents\) instead of the server, the package will fail because it will then look on the server's local drives and not see the config file unless it's saved in the exact same location, including drive letter.

The only way we've found around this is to edit the package in BIDS again and save the XML config file to a new location or to use Environment variables such as MachineName/ServerName. Unfortunately, editing the package violates Sarbanes-Oxley (aka SOX) as we're changing a software package when we move it to production and SOX says you're not supposed to do that.

Anyway, hope this tip helps someone else out.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top