Unpivot Query in ssis

Question

I am using the unpivot query in the oledb source in  data flow task 1 in SSIS. Database is Oracle

select Key,source,val
from
source unpivot include nulls
(val for (source) in
      (C1 as 'C1',
       C2 as 'C2',
       C3 as 'C3'))

I want to use the result of above query in the  data flow task 2. How can i create a table (which will the resultset of the above query) on a fly so that i can continue with the outcome of that table created on fly.

Thanks

Answer 1

It is the continuation of my question.....

Should i create temp table??

Like:

create global temporary table tester as (
select *
from
ext_npi_provider   unpivot include nulls
(description for (address) in
      (col21 as 'Business',
      col22 as 'Business',
      col29 as 'Practice',
      col30 as 'Practice'
      )))

Will this approach work?

Answer 2

You can route any rowset from a Data Flow into a RAW File Destination that you can use in other Data Flow Tasks via the RAW File Source.

Or you can simply use the exact same query as the source for flow #2.

Or you can place the logic for flow #2 inside flow #1, and use a Multicast component to feed it from the same source.

---

Talk to me now on

Answer 3

Todd, Talking about your second suggestion, how can i address the extract of dft 1 in dft 2. do i have to alias the extract in dft 1

Answer 4

No aliases required.  Just copy and paste the same query - in fact, you can copy and paste the source component itself.  It's two different sources that just happen to have the same query.  Nothing's shared to SSIS.  The only thing "shared" is the query text.

---

Talk to me now on

Answer 5

Todd, how can i use the unpivot table from dft1 into dft2

Answer 6

Actually, i want to use merge statement where i can update and insert into another table 2.

But first i have to unpivot the source table 1 before i can use it to merge records in table 2.

Please suggest the solution.

Thanks

Answer 7

If you read from a source, manupulate the records, then want to use that changed rowset for two different purposes, you can do that two ways:

1. Use a Multicast component to make two copies of the rowset (in one DataFlow) and operate independently on the two (identical) rowsets.

2. Route the rowset to a RAW file using the RAW File Destination.  In subsequent Data Flows, use the RAW File Source to read that rowset and operate on it.

---

Talk to me now on