December 29, 2009 at 10:55 pm
Hi All
I have created each XML connection.dtsconfig file that located on my Test and and Prod servers residing on their local C:\. My purpose is to move the package from Test server to Prod server and call their local C:\ connection.dtsconfig file with the SQL statement I created on the Execute SQL task. However I do not see an option in the Execute SQL task I can set to call the XML connection.dtsconfig file. My goal it to have a dynamic connection on the Execute SQL task after moving the package from TEST to PROD envrionment. Any ideas? Thanks.
December 29, 2009 at 11:40 pm
[font="Comic Sans MS"]
Hello there - you need to understand how xml configuration works.
Please see : http://www.sqlis.com/post/Easy-Package-Configuration.aspx
On the menu bar, point to DTS, Package Configurations… or right-click on the control flow design surface and select Package Configurations….
In the Package Configurations Organizer click Enable package configurations and click Add…
Click through the welcome screen and in the Configuration Type combo select XML Configuration File.
In the space for Configuration file name type C:\PackageConfigurations\Environment.dtsConfig and click Next
In the object tree browse to Connections.Destination.Properties and check the InitialCatalog & ServerName properties. Click Next
Give your configuration a name and click Finish. See…easy!!!
Your package will now pick up the values for the 2 properties at run-time. If you open the XML file in a text editor you will be able to see that the properties are currently set to whatever they were set to prior to building the package configuration.
Now you could easily move this package (and any other packages in your application) to a completely new environment and all you would have to do is change 1 property in C:\PackageConfigurations\Environment.dtsConfig. Pretty nifty! If you wanted to you could even dynamically populate the name and location of your source file at run-time.
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 30, 2009 at 12:05 am
Hi Sabya,
I have already configured the configuration file now how can I get the
"Execute SQL Task" point to this C:\PackageConfigurations\Environment.dtsConfig file. Please advise. Thanks.
December 30, 2009 at 12:50 am
Just being curious...wat exactly is your objective here..
what is your execute sql task trying to do...
as far as I can see you should be getting connection string properties (server/Dbname) from the config file and using those for your OLEDB conn manager which in turn should be used by your execute sql task.
Thanks
Pankaj
December 30, 2009 at 7:15 am
I have a sQL statement that called the constant path value that resides on a table that I am trying to input to my variable. However, the constant path value is different from TEST and PROD environment server but the field and table name stays the same on the 2 environments server. My goal is to deployed package to these 2 different enivronments server and have the Execute SQL task called the dtsConfig file that I have created that reside on their local C:\ which I have already created. On the Execute SQL task I did not see the connection on the OLEDB has a way to call dtsConfig file and I do not know how to do it and perhaps you can show me the steps.
Thanks
December 30, 2009 at 7:46 am
On the Execute SQL task I did not see the connection on the OLEDB has a way to call dtsConfig file ...
[font="Comic Sans MS"]I am totally lost here 😉
The OLEDB connection manager points to a database (which is a variable and value defined in the dtsconfig file). You need to use the exec-sql task to retrive the path details that is stored on a table and you can assign it to a user defined variable etc ...
If you have configured it right - the oledb connection manager reads from the dtsConfig file and set itself up to point to appropriate database.So - on the exec sql task you would need to point only to the olddb connection manager (which might be an alias) ...[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 30, 2009 at 8:03 am
Hi Sabya,
I am on SSIS 2008 and here is what I did on the Execute SQL task
ConnectionType = OLEDB
Connection - 1) I clicked New connection
2) Click New Button and Clicke ALL on the left hand pane
3) On the NameConnectionString and I typed in C:\PackaageConfiguration\Environment.dtsconfig. and error pop up and stated " The ConnectionString property has not been initialized."
BTW when I tried to create the PackageConfiguration I could not find the Initial Catalog on the tree as you have stated so I manually created the dtsConfig file below. Again I am on the SSIS 2008. Also do I not have a table but a variable. Will that work?
Please advise. Thanks.
- <DTSConfiguration>
- <Configuration ConfiguredType="Property" Path="\Package.Connections[EnvConn].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=[ABCTestServer];Initial Catalog=[Testdata];User ID=[PTT];password=[TEST];Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False</ConfiguredValue>
</Configuration>
</DTSConfiguration>
December 30, 2009 at 8:26 am
[font="Comic Sans MS"]
You are not doing it right.
As I said - did you read it through?
http://www.sqlis.com/post/Easy-Package-Configuration.aspx
This is for SSIS 2005 but it's no different and here is the SSIS2008 version - please read it through:
http://msdn.microsoft.com/en-us/library/cc895212.aspx
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply