SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Two different servers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

56 Posts

Posted - 07/01/2013 :  16:08:45  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/02/2013 :  04:11:53  Show Profile  Reply with Quote
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
Go to Top of Page

ugh3012
Yak Posting Veteran

56 Posts

Posted - 07/03/2013 :  11:26:58  Show Profile  Reply with Quote
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.
Go to Top of Page

ugh3012
Yak Posting Veteran

56 Posts

Posted - 07/03/2013 :  11:40:45  Show Profile  Reply with Quote
IDList is over 4,000 characters long, will that cause this problem? I saw that in the error message.
Go to Top of Page

ugh3012
Yak Posting Veteran

56 Posts

Posted - 07/03/2013 :  11:48:23  Show Profile  Reply with Quote
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>

Edited by - ugh3012 on 07/03/2013 11:49:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/03/2013 :  13:36:24  Show Profile  Reply with Quote
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
Go to Top of Page

ugh3012
Yak Posting Veteran

56 Posts

Posted - 07/03/2013 :  13:55:17  Show Profile  Reply with Quote
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. :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/04/2013 :  01:13:07  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000