Two different servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Two different servers

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources