Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with simple ETL strategy with SSIS Expand / Collapse
Author
Message
Posted Saturday, October 19, 2013 10:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, 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 ?
Post #1506407
Posted Saturday, October 19, 2013 10:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 35,369, Visits: 31,909
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506438
Posted Sunday, October 20, 2013 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1506511
Posted Sunday, October 20, 2013 11:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, 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 !
Post #1506539
Posted Monday, October 21, 2013 8:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 35,369, Visits: 31,909
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506701
Posted Tuesday, October 22, 2013 3:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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....

Post #1507033
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse