Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.
Course Of The Month
1 day, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SSIS, how to take a field from source query and move to a variable for later us
Posted on 2013-10-31
I have a simple task but can't figure out the mechanics.
I am successfully moving data from point A to point B, using a Sql command from variable, this works great.
But now I want to take one of the columns from this query and populate another variable so I can use that in another subsequent data flow.
So how do I do this ? Copy column didn't work, and Derived Column looked promising, but that didn't work as I thought it might either.
My source query returns 1 row, and I want to take the value of one of the columns and populate a variable for later use.
Here's the data I'm trying to move
HDR
Detail
Detail Child record
Trailer
I think the issue is I don't understand how SSIS works . . .
In oracle, I would write a cursor for HDR, maybe it pulls 5 records.
So I fetch first record, insert it to the dest table HDR in a different d.b.
then, while holding this record,
I pull all related detail records that have the Header.UniqueKey as FK
while processing each detail record, I similarly look for children of the detail
after all details are inserted, I'll then use the header in context and get the trailer
then fetch the next header record and begin the above sequence again.
So how do I accomplish in SSIS ?
I am successfully moving data from point A to point B, using a Sql command from variable, this works great.
But now I want to take one of the columns from this query and populate another variable so I can use that in another subsequent data flow.
So how do I do this ? Copy column didn't work, and Derived Column looked promising, but that didn't work as I thought it might either.
My source query returns 1 row, and I want to take the value of one of the columns and populate a variable for later use.
Here's the data I'm trying to move
HDR
Detail
Detail Child record
Trailer
I think the issue is I don't understand how SSIS works . . .
In oracle, I would write a cursor for HDR, maybe it pulls 5 records.
So I fetch first record, insert it to the dest table HDR in a different d.b.
then, while holding this record,
I pull all related detail records that have the Header.UniqueKey as FK
while processing each detail record, I similarly look for children of the detail
after all details are inserted, I'll then use the header in context and get the trailer
then fetch the next header record and begin the above sequence again.
So how do I accomplish in SSIS ?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4
9 Comments
Active today
Don't think you can do it in a data flow, but you can always create an SQL Task afterwards that goes something like this
In the Execute SQL Task Editor..
Set the 'Result Set' to Single Row,
SQL Statement is <air code> SELECT your_column FROM your_table WHERE whatever_results_in_a-sing
click on Result Set tab, then add your parameter.
In the Execute SQL Task Editor..
Set the 'Result Set' to Single Row,
SQL Statement is <air code> SELECT your_column FROM your_table WHERE whatever_results_in_a-sing
click on Result Set tab, then add your parameter.
Jim,
ok, so you're saying I'm not going to use similar procedure logic as in an Oracle (or Sql Server) cursor.
but surely this is a standard scenario, isn't it ?
I kind of see what you are saying, but not really getting the gist of it
Say I had 5 header records, and various details and associated trailer records.
Do I move all five records first ? and then move to the detail records ?
So after moving the 5 header records, I could then do an Execute Sql Task that pulls in all five seq_no's to a full result set (and place in a variable) ? and then my detail query says "select * from detail_table where seqno in ('full_result_set_variable
ok, so you're saying I'm not going to use similar procedure logic as in an Oracle (or Sql Server) cursor.
but surely this is a standard scenario, isn't it ?
I kind of see what you are saying, but not really getting the gist of it
Say I had 5 header records, and various details and associated trailer records.
Do I move all five records first ? and then move to the detail records ?
So after moving the 5 header records, I could then do an Execute Sql Task that pulls in all five seq_no's to a full result set (and place in a variable) ? and then my detail query says "select * from detail_table where seqno in ('full_result_set_variable
Active today
In SSIS this will be the same process regardless of Oracle or SQL server.
>that pulls in all five seq_no's to a full result set (and place in a variable
The problem here is that SSIS variables are scalar (i.e. single value) unless it's an Object, and what you are proposing is to take a set, and grab a column which may or may not have the same value in every row, and assign it to that variable.
>select * from detail_table where seqno in ('full_result_set_variable
Now THAT would be a lookup transformation, which looks up the list from Oracle, and is joined to the earlier SQL set on whatever rows match.
>that pulls in all five seq_no's to a full result set (and place in a variable
The problem here is that SSIS variables are scalar (i.e. single value) unless it's an Object, and what you are proposing is to take a set, and grab a column which may or may not have the same value in every row, and assign it to that variable.
>select * from detail_table where seqno in ('full_result_set_variable
Now THAT would be a lookup transformation, which looks up the list from Oracle, and is joined to the earlier SQL set on whatever rows match.
>>In SSIS this will be the same process regardless of Oracle or SQL server.
- ok, well, that sounds promising
but I think I need to just scratch everything and ask, how do I do this ?
say I have five header records and various details for each
HDR
Detail
Detail Child record
Trailer
does SSIS insert all five header records first ? or am I looping through one header record at a time and then getting the details and finally the trailer ? If so, I think I'm back to my original question, how do I grab the seqno of each header record as it's being processed so I can get the associated details and trailer ?
(I could knock this out in 10 minutes in oracle, but I'm trying to use SSIS . . . )
- ok, well, that sounds promising
but I think I need to just scratch everything and ask, how do I do this ?
say I have five header records and various details for each
HDR
Detail
Detail Child record
Trailer
does SSIS insert all five header records first ? or am I looping through one header record at a time and then getting the details and finally the trailer ? If so, I think I'm back to my original question, how do I grab the seqno of each header record as it's being processed so I can get the associated details and trailer ?
(I could knock this out in 10 minutes in oracle, but I'm trying to use SSIS . . . )
Active today
>does SSIS insert all five header records first ?
SSIS can't handle different row types in a data flow, such as headers and footers, unless these rows are distinct enough that you can pick them off with some kind of conditional split, or custom code.
This really must be done after the data is loaded into a staging table, editing it using a script task and custom T-SQL.
So, I declare dibs on 'it can't be done' as the answer to your question as it is defined.
>(I could knock this out in 10 minutes in oracle, but I'm trying to use SSIS . . . )
If this is correct, then I'd go with the Oracle solution.
SSIS can't handle different row types in a data flow, such as headers and footers, unless these rows are distinct enough that you can pick them off with some kind of conditional split, or custom code.
This really must be done after the data is loaded into a staging table, editing it using a script task and custom T-SQL.
So, I declare dibs on 'it can't be done' as the answer to your question as it is defined.
>(I could knock this out in 10 minutes in oracle, but I'm trying to use SSIS . . . )
If this is correct, then I'd go with the Oracle solution.
>>If this is correct, then I'd go with the Oracle solution.
- except that SSIS is the tool of choice now plus I need to learn it (to keep my job).
I understand SSIS can't handle different row types in a data flow, but I was planning on a separate data flow for each record type.
hmmmm . . .
what about this
Execute Sql Task
- get last_pull_from_date
Data Flow 1
- select * from header where insert_date > last_pull_from_date (5 records)
Data Flow 2
- select * from
header a inner join detail b on a.hdr_seqno = b.hdr_seqno
where hdr.insert_date > last_pull_from_date (XXXX records)
Data Flow 3
- select * from
header a inner join trailer b on a.hdr_seqno = b.hdr_seqno
where hdr.insert_date > last_pull_from_date (XXXX records)
--------
this only works because I am moving data as is, so the seqno's match up. So the seqno on all detail records is the same between source and dest. If I was inserting a seqno based on destination, then I couldn't do the above.
- except that SSIS is the tool of choice now plus I need to learn it (to keep my job).
I understand SSIS can't handle different row types in a data flow, but I was planning on a separate data flow for each record type.
hmmmm . . .
what about this
Execute Sql Task
- get last_pull_from_date
Data Flow 1
- select * from header where insert_date > last_pull_from_date (5 records)
Data Flow 2
- select * from
header a inner join detail b on a.hdr_seqno = b.hdr_seqno
where hdr.insert_date > last_pull_from_date (XXXX records)
Data Flow 3
- select * from
header a inner join trailer b on a.hdr_seqno = b.hdr_seqno
where hdr.insert_date > last_pull_from_date (XXXX records)
--------
this only works because I am moving data as is, so the seqno's match up. So the seqno on all detail records is the same between source and dest. If I was inserting a seqno based on destination, then I couldn't do the above.
I ended not needing a "for-each loop container", but thanks for the tips, this helps me get the jist of how SSIS operates.
Featured Post
Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month1 day, 21 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.