SSIS Configuration blues
-
I've had nothing but problems trying to get my packages to use various types of configurations. Anybody know of any good resources? Here are two specific problems I have:
1 - Environment Variables will only affect package variables. I set up an env var called FileName and assign it a value of c:\somefile.txt. In my package I assign the ConnectionString property of my Flat File Connection to point to that env var. I ran the packge from dtexec, dtexecui and from Sql Studio and it doesn't pick up the env var. BUT, if I change my package so that ConnectionString property is an expression that reads from a package variable, and that package variable points to the env var, it works. Shouldn't both of those scenarios work?
2 - What's the proper syntax for a Registry entry? If you go into Package Configurations and select a new Registry Entry, you get a text box to type a registry entry. I've tried everything I can think of and nothing seems to work. Anyone get know the correct syntax or get registry entries to work?
3 - In XML config files, my Oracle connection info just won't work. I don't know if it's really an Oracle problem or an XML config problem, but I can run the packages fine in BI Studio, run them from dtexec/dtexecui with their default values, but when I add an XML config file I can't connect to my Oracle sources.
Any help would be appreciated.
-
Don't know about 1 and 2.
Regarding 3...what error message are you getting? I am storing my connection strings for Oracle (and lots of other things) in config files and its working fine.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I've answered most of my own questions - thru trial and error. Thought I'd post the details for anyone who's interested.
Environment Variables for Configuration
Be careful how and where you launch executables (dtexec/dtexecui/Sql Studio/BI Studio) that will reference env vars. Executables (or processes if you prefer that term) inherit env vars from their parent process. So, if you launch BI Studio and start working on your package and then go over to the control panel and add a few env vars, BI Studio won't recognize them. If you right-click on the Control Flow surface and step through the Package Configurations wizard, your newly added env vars won't be in the drop down list. You'd have to close down BI Studio and re-laucnh it. You'll have the same problem if you have Sql Studio running, add some env vars, and then you right click a package in the Integration Services Object Explorer and click "Run Package". This will kick off dtexecui, but it will be inheriting the environment from Sql Studio (which inherited from the OS pre env vars) and it won't recognize the new env vars.
In hindsight, this all seems pretty obvious but it was confusing at the time. I'd also like to pass on a useful utility I found called "Setx.exe". It comes with Windows 2003 and it's available for download from Microsoft for all other OS's. It allows you to set system level environment variables from the command line. The "set" command at the command line only sets user environment variables. "Setx" also lets you set Registry values from the command line.
Registry Configurations
Still haven't been able to figure the correct syntax for this. If anyone knows, please post it.
XML Configurations
Ok, my problems here are due to my confusion between Direct and Indirect Confiurations. If you check the BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d20e0311-1fc9-4ddc-a381-6d127cf11b69.htm) you get a couple quick paragraphs that don't really tell you much. I think MS needs to do some significant doc work here to fully explain the multiple ways to configure a package.
When you are in BI Studio and working on your package, you can right-click the Control Flow surface and click Package Configurations. You then add an XML Configuration file and specify which properties to store in the file. Ok, when you do this, the path to the config file is hard coded into your SSIS package. If you check out the XML source of your package you'll find something like this: <DTS
roperty DTS:Name="ConfigurationString">D:\Code\SSIS Projects\Configuration Test\Env2.dtsConfig</DTSroperty>. This is a Direct Configuration.My Oracle problems are due to me creating an XML Configuration file from BI Studio and choosing to store every Oracle connection property in the config file. When I ran my package I would get errors to the affect of Error: System.Data.OracleClient.OracleException: ORA-01005: null password given; logon denied. I'm not sure why the package wouldn't run, I had all the correct info in the config file. So I started hand editting the config file and I removed all the properties except ServerName, UserName and Password. Re-ran my package and voila it worked! I'm speculating here, but I think there may have been some conflict between the ConnectionString property and Password property. Oracle may share some blame here, too, I've had my share of Oracle issues.
Here's another tip, you don't necesarily need to specify a config file in every packge (in BI Studio). For example, I have many packages that all access the same Oracle database and I don't want to use BI Studio to add an XML Configuration file to every package (not to mention the path of the config file will get hard coded into every package). Instead, I call my Oracle connection the same name in every package. Then I create one config file and have all the packages reference that one config file. Kirk Haselden has a decent blog entry about the syntax for config files (http://sqljunkies.com/WebLog/knight_reign/search.aspx?q=configuration&p=1). I have found that it's easier to create a dummy config file and then hand edit it as you need to.
-
Jeff,
That was a great post - thanks for that.
The ConnectionString property is an amalgamation of all the other properties of the connection manager. I don't know which has priority but I DO know that storing only ConnectionString in your configuration is sufficient and indeed the correct thing to do.
If the docs are inadequate then please use the Send Feedback link to inform the doc team.
The problem around the setting of env vars and BI Studio not knowing about them caused me no end of problems one day until I realised what was going on. Its really annoying so yuor post here is really useful - hopefully people will realise the problem before it happens.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Hi....Jeff.
I found the way about #2
"2 - What's the proper syntax for a Registry entry? If you go into Package Configurations and select a new Registry Entry, you get a text box to type a registry entry. I've tried everything I can think of and nothing seems to work. Anyone get know the correct syntax or get registry entries to work?"
Step 1) Add registry Key in HKEY_CURRENT_USER in regsvr32.exe
ex) HKEY_CURRENT_USER\SSISPackages
Step 2) add string value which name is "value" at [HKEY_CURRENT_USER\SSISPackages]
Step 3) BIDS -> Configurations -> Registry Entry and type "SSISPackages"
You can use other Key name...
But you should place it in HKEY_CURRENT_USER and set the string value name "value" ....
HTH
Daeseong Han
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply