Database Connections as a Variable - Help Please
-
Hi!
I am using Execute SQL Task to update a table, but I need to reference another database to complete the update. I would like to avoid hard-coding the database connection in the SQL statement so I'm trying to use a variable. I have the variable value in the Configuration File:
and in the Variables list (listed as SourceDB, Package scope, String, convSample value) of the SSIS package. I'm also using OLE DB connection, Direct Input, and my SQL update statement is:
update electronic
set locator_id = ee.locator_id
from electronic e
join ?..electronic ee
on e.email_id = ee.email_id
For parameter mappings, I have User::SourceDB, Input, VARCHAR, and parameter name of 0. (Sorry, I'm trying to list as much information as I can so you can see what I did)
Is there something that I didn't do or didn't do right? Can this even be done?
Thank you in advance!
-
i'm not entirely sure the execute sql task is quite as dynamic as you hope.
one solution would be to use a data flow task to write the data from the database held in the variable (change the connection string on the connection with an expression pointing to the variable) into a holding table in the destination database.
if you have more than one database from which you need to grab data you can loop through using a foreach loop, changing the source connection on the fly.
then, have an execute sql task to run the update from this holding table to the source table. this update sql will then be fixed and no need to try and make it dynamic.
tom
Life: it twists and turns like a twisty turny thing
-
You can set the connecting string for a database connection as a variable by setting in an expression.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
You could store the query in a variable and evaluate as an expression...
Inside the expression you can format it as you want...
-
Thank you for your suggestions! I'm trying the expression method and I have 2 variables within the expression.
@[user::variable1] = "update email set locator_id = ee.locator_id from electronic e join " + @[user::variable2] + "..electronic ee on e.email_id = ee.email_id"
I set @[user::variable1] EvaluteAsExpression to True, but I receive an error message which is:
Error: Failed to lock variable "update email set locator_id = ee.locator_id from electronic e join convSample..electronic ee on e.email_id = ee.email_id" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created".
Does anyone happen to know how to fix this error? The expression is correct and is what I want. Thank you.
-
Does the "variable2" exists in the context that you are trying to evaluate? and still exists?
Well, now that i see the error message:
"update email set locator_id = ee.locator_id from electronic e join convSample..electronic ee on e.email_id = ee.email_id"
It gets the "convSample" value, but its having problems with "variable1"...
-
Yeah... I don't understand what I'm doing wrong. I declared the variable in the SSIS menu --> Variables and I set the variable1 to hold the expression... I'm just baffled...
-
do you have the text "@[user::variable1] =" within the expression for the variable?
if this is the case, then remove it and just have "update email set locator_id = ee.locator_id from electronic e join " + @[user::variable2] + "..electronic ee on e.email_id = ee.email_id" in the expression builder.
tom
Life: it twists and turns like a twisty turny thing
-
Thank you so much!! It works!! :w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply