SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with simple ETL strategy with SSIS


Need help with simple ETL strategy with SSIS

Author
Message
blasto_max
blasto_max
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 480
I have the following ETL scenario and I need some suggestions on how to go about it. I have a source table Tbl_S and a target table Tbl_T. There is a table Tbl_ETL that has two columns - Col_S and Co_T, ie source and target respectively. This table tells me which source column is "associated" with which target column, that is Col_S contains the names of all columns of Tbl_S and Col_T contains the names of all columns of Tbl_T.

Example -

Tbl_S -

Col1S,Col2S,Col3S...etc.
Some rows here. Dont know what the data in those rows
represents. eg.Col1S has 11092011. Is that date or
some ID ? don't know.

Tbl_T

ID,Date,Date_Updated...etc
no data here !

So, ETL table is like -

Tbl_S, Tbl_T
Col1s, Date
Col2s, Date_Updated
Col3s, ID
...etc
Now the problem is that tomorrow, the "associations" could change ! Col1S could mean ID instead of Date. YES ! this is a very weird situation and I have to to live with it. Also, the datatypes of source and target columns are not the same, ie not all varchar or all date.

Two questions -

1 - Is the problem clear or have i missed something ?

2 - Any strategy on how this could be done using only SSIS ?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85428 Visits: 41078
I don't use SSIS, so no help there. I just thought I'd mention that this would be incredibly easy and simple to do with a little Dynamic SQL.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27237 Visits: 13268
In contrast with Jeff, I know (quite) a lot about SSIS.
DO NOT USE SSIS FOR THIS.

Use dynamic SQL for this (as Jeff mentioned).
If I would need to do this in SSIS, I would do it with dynamic SQL in an Execute SQL Task.

(alternatively, you could also use the .NET SQLBulkCopy class in a Script Task)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
blasto_max
blasto_max
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 480
Koen Verbeeck (10/20/2013)
In contrast with Jeff, I know (quite) a lot about SSIS.
DO NOT USE SSIS FOR THIS.

Use dynamic SQL for this (as Jeff mentioned).
If I would need to do this in SSIS, I would do it with dynamic SQL in an Execute SQL Task.

(alternatively, you could also use the .NET SQLBulkCopy class in a Script Task)


Yes, thats what I am doing now. Dynamic SQL inside SSIS. So time consuming !
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85428 Visits: 41078
blasto_max (10/20/2013)
Koen Verbeeck (10/20/2013)
In contrast with Jeff, I know (quite) a lot about SSIS.
DO NOT USE SSIS FOR THIS.

Use dynamic SQL for this (as Jeff mentioned).
If I would need to do this in SSIS, I would do it with dynamic SQL in an Execute SQL Task.

(alternatively, you could also use the .NET SQLBulkCopy class in a Script Task)


Yes, thats what I am doing now. Dynamic SQL inside SSIS. So time consuming !


"Time consuming" how?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1039 Visits: 902
You can do this in SSIS (I am about to emark on a DW build that will use this principle!)

You can use the unpivot task (it is a little confusing at first but there are plenty of good examples if you google it)

Some things to consider
You need a common anchor (e.g. source record ID)
You can only unpivot columns of the same type
With strings (DT_STR) they all need to be the same length to unpivot them - Grrrrrr!

So;
Change the output lengths of the strings at the top of the stream
Multicast the stream; one for each data type
Generate an unpivot for each data type that you need.
->Passthrough the anchor values only
->unpivot the columns into with a target column of FieldName
->Leave the unpivot value as the incoming field name

Union the streams: you will have to put each datatype into a different column in the unioned stream

This will change the upstream dataset

ID,strString,intValue,dteDate
1,Bob,123,01/07/2013
2,Jim,999,31/08/2013

into

1,strString,Bob
1,intValue,,123
1,dteDate,,01/07/2013
2,strString,Jim
2,intValue,,999
2,dteDate,,,31/08/2013

You can now use lookup to translate the inbound column names into outbound column names and then use the unpivot function to rebuild the data stream.

I'm not saying it will be quick mind....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search