December 3, 2007 at 1:00 am
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:
SELECT name, age FROM DB1..Person t1
INNER JOIN DB2..PersonToo t2
ON t1.PersonID = t2.PersonID
I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation.
Greetz,
Hans Brouwer
December 3, 2007 at 3:11 am
I think you must use "SQL command from variable" option when accessing this query from OLE DB Source an point to variable name from Variable name drop down list. Value for this variable should be supplied by script component data flow before it may be used OLE DB Source.
December 3, 2007 at 7:13 am
Tnx for answering, Pow, but I'm not sure what you mean. I don't think there is a variable which can be attached to a part of an SQL statement.
1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters...
It all sounds very messy, and I still don't know how
Greetz,
Hans Brouwer
December 3, 2007 at 8:18 am
Please put OLE DB Source component on the Data Flow tab surface, double click on it and click on "Data access mode" drop down list, and the last option is "SQL Command from variable". If you select it then "Variable Name" drop down list will be displayed and then select variable name which contains your select statement.
December 3, 2007 at 11:29 pm
I see what you mean. I'll give that a try.
Tnx,
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply