| Author |
Topic |
|
ugh3012
Yak Posting Veteran
62 Posts |
 Posted - 2013-07-01 : 16:08:45
|
On one server, there is a database that has list of IDs that will be used on a different server. I am trying to figure out how to put this in SSIS.
Server_A
Select ID from table_A where……. Will return a list of IDs.
Server_B
Select * from table_D where subID in (** List is from above **)
I have full access to Server_A, and only view access to Server_B.
How can I do that in Data Flow process? I have no clue as I never used SSIS, I am looking for directions at this point. If you have a specific questions, I will answer it tomorrow.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 04:11:53
|
Do the following in SSIS
1. Create a variable of type object. this can be done by right clicking on the package and choosing variable option. The variable windows options where you can give name, type and default value if required
2. Create a execute sql task with OLEDB connection to ServerA. then use query as
Select ID from table_A where…….
Ser Resulset property to Full Resultset. Go to ResultSet tab and map the ID column to Object variable created above
3. Use a For Each Loop with ADO.NET enumerator and map to object variable. Now create a integer variable IDVal to hold ID value and map it inside for each loop to get iterative value during loop
4. Create a String variable of name IDList to hold list of IDs
5. Create a script task inside the loop and declare IDList inside it in read write mode. then add code to append each value of ID variable to it like
Dts.Variable("IDList").value = Dts.Variable("IDList").value.ToString() + "," + Dts.Variable("IDVal").value.ToString()
6. Create a variable SQLString and make EvaluateAsExpression property true for it. Then in expression builder write expression as
"Select * from table_D where subID in (" + @[User::IDList] + ")"
7. Create a execut sql task outside loop and use OLEDB connection to Server_B
Use SQLSourceType property value as Variable for the task and map it to the variable SQLString
you'll get resultset inside SQLTask which you can put in a object variable for future consumption or make it into an INSERT...SELECT to put it to table.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-03 : 11:26:58
|
Thanks. I have been working on this for the last two days. I have few porblems.
When I added (" + @[User::IDList] + ")" to SQL String, the values are not coming in. I see them in quick watch, so I know it has values. This may has something to do with mappings the variables in Execute SQL Task. Setting up the maps in Parameter mapping are confusing, so any help here is appreciated.
|
|
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-03 : 11:40:45
|
| IDList is over 4,000 characters long, will that cause this problem? I saw that in the error message. |
|
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-03 : 11:48:23
|
| I did some research. There is no limit on string, but it is used in expression. The expression has a limit of 4,000 characters. Looks like I will have to do this in a different way, any suggestions> |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-03 : 13:36:24
|
quote:
Originally posted by ugh3012
I did some research. There is no limit on string, but it is used in expression. The expression has a limit of 4,000 characters. Looks like I will have to do this in a different way, any suggestions>
yep. put IDs in a working table. then use query as below
Select * from table_D where subID in(select ID from Table)
inside execute sql task
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-03 : 13:55:17
|
quote:
yep. put IDs in a working table. then use query as below
Select * from table_D where subID in(select ID from Table)
inside execute sql task
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
I cannot do that because it is coming from two different servers. That is why I was trying to store it in a variable to be used in a different task. :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-04 : 01:13:07
|
quote:
Originally posted by ugh3012
quote:
yep. put IDs in a working table. then use query as below
Select * from table_D where subID in(select ID from Table)
inside execute sql task
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
I cannot do that because it is coming from two different servers. That is why I was trying to store it in a variable to be used in a different task. :(
Why? still you should be able to merge them and do population inside a data flow task.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
Two different servers