June 6, 2012 at 12:29 pm
Hello,
I made this post yesterday:
http://www.sqlservercentral.com/Forums/Topic1311439-364-1.aspx
I am endeavoring to store a connection string that will be used by my OLEDB Connection manager using a config file of some nature. I already am using a Script Task to read variables from a SQL table. For this reason, I thought I would use an XML config file for the connection string. (This is because I can't figure out how I can define a connection string in a SQL table and read its value--without already knowing the connection string. In other words, the old chicken-and-the-egg conundrum seems applicable.)
I've been reading so many sites that discuss setting a connection string from an XML file that my eyes are ready to pop out of my head. However, I still can't get it to work. I'm sure I forgot something elementary, but don't know what it is.
Here are my steps so far:
1. Enabled package configurations, went through the wizard and created an XML file. This file has all of the exposed properties of the OLEDB connection manager.
2. Created a variable of project scope called ConnectionString.
3. Edited the properties of the OLDEDB connection manager. Beneath "expressions" in the property window is "ConnectionString." I added this expression:
@[User::ConnectionString]
I am trying to figure out how to set the variable I created to read the ConnectionString from the XML config file. If I don't need a variable for this, then I wish to have the OLEDB connection manager read the XML file.
How do I go from the XML config file into a user-defined variable which in turn is used by the OLDEDB connection manager?
thanks,
June 7, 2012 at 12:09 am
The variable part is unnecessary. You can directly configure the connectionmanager using the XML config file.
If you go to package configurations and go to the wizard, make sure you select the connectionstring property of your connection manager. This will be written by the wizard to the XML config file.
You need to end up with something like this:
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[myConnectionManager].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=myServer;Initial Catalog=myDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 7, 2012 at 7:25 am
Thanks for the response. I've been trying to confirm that the package was actually using the connection string from the XML config file. It now appears to be doing so.
I've been working in BIDS and copying the package to a test server where I run it. When I was running the package from the test server I was including one of the two config files I have via the "configurations" tab. (One config file is good; the other is deliberately bad). However, it seems the package doesn't care at all what I include in the configurations tab at run time, if I have already saved a package configuration in BIDS.
If in BIDS, I save the bad XML config file and export to the server and run, it doesn't work (as expected). If I save the good XML config file and export and run, it does work (as expected). So, it seems that the XML config file is working as expected.
What threw me was that Configurations tab when you execute the package. If you have already defined configurations in BIDS, what you specify here is largely ignored.
thanks for the help!
June 7, 2012 at 1:47 pm
cafescott (6/7/2012)
Thanks for the response. I've been trying to confirm that the package was actually using the connection string from the XML config file. It now appears to be doing so.I've been working in BIDS and copying the package to a test server where I run it. When I was running the package from the test server I was including one of the two config files I have via the "configurations" tab. (One config file is good; the other is deliberately bad). However, it seems the package doesn't care at all what I include in the configurations tab at run time, if I have already saved a package configuration in BIDS.
If in BIDS, I save the bad XML config file and export to the server and run, it doesn't work (as expected). If I save the good XML config file and export and run, it does work (as expected). So, it seems that the XML config file is working as expected.
What threw me was that Configurations tab when you execute the package. If you have already defined configurations in BIDS, what you specify here is largely ignored.
thanks for the help!
Package configurations and the configurations tab don't work as you would expect.
Read the paragraph Understanding How SSIS Package Configurations Are Applied at Run Time of the following MSDN article:
Read it? Read it again 😀 It took me a few times to fully understand what is happening.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply