I am trying to recode a SQL2K DDQ into an SSIS Dataflow. I have no issue recoding it in SSIS except I am not sure that I am doing it correct way.
My DDQ has source,destination, update/delete/insert statements and few lookups. I have used activex script for the transformation because I have atleasst 10 If conditions. For every "if condition" the destination columns are popuated with different lookups/source columns and constant values.
Now When I start doing it with SSIS I have to use at least 10 Conditional split. and then at least one lookup,one OLEDBCommand and one OLEDB Destination for each of them . that brings my count of DF objects to 30-40
It makes my data flow to complex with two many objects. Earleir I could do whole of this in one sngle DDQ. It makes me think if I am doing it the correct way. should I be using Activex Script Task to these kind of activity.
Any advice would be appreciated.
Cheers,
siaj
SSIS Dataflow vs SQL2K DDQ
mousy22
Thank you Jammie and Simon...
Jammie you are correct .. I can use multiple outputs Conditional split( I realized that it bit late :) )
but I would rather go by Simon's way of scripting it because I have a single destination table and the different treatment I have to have for the columns..
Cheers,
siaj
tez_uk
I'd do that in script component.
You don't have control over the mapping of columns in a conditional split.
The main question is, are you splitting your input into multiple destinations or is it that it's going to the same destination and you just need to format the columns based on the one column.
If you have multiple destinations the the conditional split is what you want. If you have one destination then the script component is more like what you want
Paul Programmer
Thanks for replying...
I thought of putting all the 10 conditions in a single condional split but then as I said I have a diffrent treatment for each of the If condition. So say my "If Condition" is as
IF "Plan_Code" = "A"
Then
DestnCol1 = Const1
DestnCol2 = SouceCol1
DestnCol3 = vlookupvalue1
IF "Plan_Code" = "B"
Then
DestnCol1 = SouceCol1
DestnCol2 = Const2
DestnCol3 = vlookupvalue2
IF "Plan_Code" = "B"
Then
DestnCol1 = SouceCol1
DestnCol2 = vlookupvalue2
DestnCol3 = Const3
........
.............. _ - - - - - -
Now since Conditional split has only single output, I can only map it to one OLEDB Command or one OLEDB destination which can take care of only one condition (unless somehow I get through writing a big complex case statement query in OLEDB Command)
Cheers,
siaj
Ed Casey
Why have you got to use 10 conditional splits Can you not just put all your conditions into 1 single conditional split transform
-Jamie
yusufiye
Mityped something... the IF conditions would be like
IF "Plan_Code" = "A"
IF "Plan_Code" = "B"
IF "Plan_Code" = "C"
GRAYDEMON
Er....conditional split has multiple outputs!
-Jamie