February 22, 2011 at 11:21 am
Hi,
SSIS - I put a resultSet in a variable. How do I select from that resultset variable from a different Execute SQL Task.
I need to do this becuase the results from a query is on a different NON-linked server. SO I put the results in a variable using an Execute SQL Task.
Now from another SQL task connected to another server and database,
how do I select from that query resultset variable from the first Execute SQL Task?
I want to go:
select * from USER::RESULTSET
Thanks in advance.
Adam
February 22, 2011 at 11:48 am
I assume you've dropped it to a Recordset Destination? The short answer is: you can't, not easily.
You create a foreach loop (the way that's built in to work with it) and loop through it row by row, or you have to build out some intricate VB Script to turn it into DataSource in a different Data Flow, but that requires datastream manipulation, not SQL.
If you want to call SQL against the object, you need to drop it to a staging table, at the minimum, so your SQL can work against it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 11:51 am
Craig Farrell (2/22/2011)
I assume you've dropped it to a Recordset Destination? The short answer is: you can't, not easily.You create a foreach loop (the way that's built in to work with it) and loop through it row by row, or you have to build out some intricate VB Script to turn it into DataSource in a different Data Flow, but that requires datastream manipulation, not SQL.
If you want to call SQL against the object, you need to drop it to a staging table, at the minimum, so your SQL can work against it.
You can also create a script task and create an ADO.NET dataset/datatable object and populate that one with the recordset.
You can do a lot with it, but it rapidly turns into an ugly cursor.
(unless you use the SQLbulkcopy adaptor)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2011 at 11:58 am
Koen Verbeeck (2/22/2011)
Craig Farrell (2/22/2011)
I assume you've dropped it to a Recordset Destination? The short answer is: you can't, not easily.You create a foreach loop (the way that's built in to work with it) and loop through it row by row, or you have to build out some intricate VB Script to turn it into DataSource in a different Data Flow, but that requires datastream manipulation, not SQL.
If you want to call SQL against the object, you need to drop it to a staging table, at the minimum, so your SQL can work against it.
You can also create a script task and create an ADO.NET dataset/datatable object and populate that one with the recordset.
You can do a lot with it, but it rapidly turns into an ugly cursor.
(unless you use the SQLbulkcopy adaptor)
True, but I believe we're actually overlapping with what we're discussing, when I mention the data-script source. Check out this article:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 12:02 pm
Craig Farrell (2/22/2011)
Koen Verbeeck (2/22/2011)
Craig Farrell (2/22/2011)
I assume you've dropped it to a Recordset Destination? The short answer is: you can't, not easily.You create a foreach loop (the way that's built in to work with it) and loop through it row by row, or you have to build out some intricate VB Script to turn it into DataSource in a different Data Flow, but that requires datastream manipulation, not SQL.
If you want to call SQL against the object, you need to drop it to a staging table, at the minimum, so your SQL can work against it.
You can also create a script task and create an ADO.NET dataset/datatable object and populate that one with the recordset.
You can do a lot with it, but it rapidly turns into an ugly cursor.
(unless you use the SQLbulkcopy adaptor)
True, but I believe we're actually overlapping with what we're discussing, when I mention the data-script source. Check out this article:
Your suggestion is using the recordset as a source in the dataflow using a script component, so that the data can be handled in the dataflow. My "suggestion" is a script task in the control flow that handles everything in .NET. You'll never hear me say that is the best option, I'm just stating it as an alternative 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2011 at 12:02 pm
Are you expecting a lot of volume? If it's small you could use the temporary storage and simply read it back into the flow.
Steve.
February 22, 2011 at 12:27 pm
Koen Verbeeck (2/22/2011)
Craig Farrell (2/22/2011)
Koen Verbeeck (2/22/2011)
Craig Farrell (2/22/2011)
I assume you've dropped it to a Recordset Destination? The short answer is: you can't, not easily.You create a foreach loop (the way that's built in to work with it) and loop through it row by row, or you have to build out some intricate VB Script to turn it into DataSource in a different Data Flow, but that requires datastream manipulation, not SQL.
If you want to call SQL against the object, you need to drop it to a staging table, at the minimum, so your SQL can work against it.
You can also create a script task and create an ADO.NET dataset/datatable object and populate that one with the recordset.
You can do a lot with it, but it rapidly turns into an ugly cursor.
(unless you use the SQLbulkcopy adaptor)
True, but I believe we're actually overlapping with what we're discussing, when I mention the data-script source. Check out this article:
Your suggestion is using the recordset as a source in the dataflow using a script component, so that the data can be handled in the dataflow. My "suggestion" is a script task in the control flow that handles everything in .NET. You'll never hear me say that is the best option, I'm just stating it as an alternative 🙂
Ooooooh, my bad, I misread your comment earlier, apologies. You're right, that's also an option.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 7:46 pm
Thanks ALL!
Someone mentioned the for each loop container and vb script. But I do not want to do that becuase I seems like a lot of work becuase I need to integrate the results into another Exec TSQL tasks' stored proc. I can make that sproc text if I need to.
But the Staging table. How to do that??
I mean I have to be able to access it in SSIS from a connection from server2. I would be SUPER happy to have a staging table and select it from another execute sql task that is connected to the other server 1 in order to manipulate it there where there is a tsql query that was written prior to me.
ultimatley I need to join this resultset from a table from server1 to the table in server2.
why do they make it so hard, why can't I in an Execute sql task be able to select from the recordset vairable.
again from SQL task 2.
select columns from @[user::MMR_EDW] where contact = @contract and Run_date = @Run_date.
So I assume temp tables are unreachable from 1 exec SQL task to each other SQL task. Am I wrong???
Is that what you meant by a staging table?
If it is a base table I still have the problem (unless the staging table is in the ssis package) of reaching it from the OLE DB connection of the other server.
Or is there someway to insert the data from my result set variable into a temp in server2's SQL task tsql code?
then do a (very simplified here)
select a.col,b.col
from #staging a join staging b
on a.id = b.id
THANKS SO MUCH!
February 22, 2011 at 8:07 pm
oh and Steve, its about 20,000 rows and 60 columns.
But I want to try it in case.Maybe it could hold it. How would I do that?
February 22, 2011 at 11:48 pm
adam spencer (2/22/2011)
But the Staging table. How to do that??I mean I have to be able to access it in SSIS from a connection from server2. I would be SUPER happy to have a staging table and select it from another execute sql task that is connected to the other server 1 in order to manipulate it there where there is a tsql query that was written prior to me.
That is possible. Just store the results of your sp into a temporary table (or a real staging table). Then select from this table in your second Execute SQL Task.
This article describes how to store the results of the sp into a temp table:
adam spencer (2/22/2011)
why do they make it so hard, why can't I in an Execute sql task be able to select from the recordset vairable.again from SQL task 2.
select columns from @[user::MMR_EDW] where contact = @contract and Run_date = @Run_date.
Well, you just can't 😀
That is because the SQL statement is handled through the OLE DB provider, which of course never has heard of SSIS variables before.
The SQL Server team should make something that replaces the name of the variable with it's value before handing it over to OLE DB.
adam spencer (2/22/2011)
So I assume temp tables are unreachable from 1 exec SQL task to each other SQL task. Am I wrong???Is that what you meant by a staging table?
Luckily, you are wrong. The only thing you have to do is to make sure you are using the same connection manager in both Execute SQL Tasks and that the property RetainSameConnection of that connection manager is set to TRUE.
Give it a try, and let us know how it works out.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2011 at 1:15 am
adam spencer (2/22/2011)
But the Staging table. How to do that??I mean I have to be able to access it in SSIS from a connection from server2. I would be SUPER happy to have a staging table and select it from another execute sql task that is connected to the other server 1 in order to manipulate it there where there is a tsql query that was written prior to me.
ultimatley I need to join this resultset from a table from server1 to the table in server2.
Alright, a few confusion points on my part here.
First, to generate a staging table you would simply CREATE TABLE before deployment, truncate when you run this process, and fill it using a dataflow. The table is then available via standard connections.
Now, for multi-server connections, you would typically use a linked server from your procs, like any other time.
If you wanted, however, to do some kind of SSIS only work, you could do a merge join in the datastreams of the dataflow. This is usually non-optimal, as you have to control sorting and sorting stops the dataflow until all records are sorted, then continues, however it's an option.
Though, a question. Why not have the staging table on server1 along with the original, do your processing there, and THEN move the results to server2 via another dataflow?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply