Transforming flatfiles in SSIS
Hi all,
Ich habe 2 csv files named Old1 and Old2.
Old1 has the following column names: A, B, C, D, E, F and Old2: A, D, R, G
Now I have 2 new csv files. The first one New1 has the following column names: H,I,J,K,L,M , Y and the second one New2 : H, O, X, N, I, Q
The mappings between Old1, Old2, New1 and New2 are as follow:
A – H, B – I , C – J , E – Q, F – N and D –O , R – X , G – Y
The whole SSIS packages, database tables and stored functions were built up with the column names of Old1 and Old2 and shouldn’t be changed.
How can I in SSIS transform each new csv file so that I become each old file (Old1 and Old2) again in order to keep using my other SSIS packages and stored functions?
What I tried to do so far is in the picture (TransformFlatfiles.PNG) enclosed. I don't know if it makes sense ... I can't even managed to find out how to use the conditional split in order to retrieve Old1 and Old2 ...
Thank you
TransformFlatfiles.PNG
Ich habe 2 csv files named Old1 and Old2.
Old1 has the following column names: A, B, C, D, E, F and Old2: A, D, R, G
Now I have 2 new csv files. The first one New1 has the following column names: H,I,J,K,L,M , Y and the second one New2 : H, O, X, N, I, Q
The mappings between Old1, Old2, New1 and New2 are as follow:
A – H, B – I , C – J , E – Q, F – N and D –O , R – X , G – Y
The whole SSIS packages, database tables and stored functions were built up with the column names of Old1 and Old2 and shouldn’t be changed.
How can I in SSIS transform each new csv file so that I become each old file (Old1 and Old2) again in order to keep using my other SSIS packages and stored functions?
What I tried to do so far is in the picture (TransformFlatfiles.PNG) enclosed. I don't know if it makes sense ... I can't even managed to find out how to use the conditional split in order to retrieve Old1 and Old2 ...
Thank you
TransformFlatfiles.PNG
ASKER
I can't map directly New1 to Old1 because there are some columns from New1 (H,I,J) which map to Old1(A,B,C) AND also columns from New2(Q,N) which map to Old1(E,F).
Mapping directly New2 to Old2 isn't possible either because there are columns from New1 and New2 which map to Old2 ...
In this conditions it isn't possible to use the Component Script in Data Flow or is it?
Thank you
Mapping directly New2 to Old2 isn't possible either because there are columns from New1 and New2 which map to Old2 ...
In this conditions it isn't possible to use the Component Script in Data Flow or is it?
Thank you
I mean that it is Script Component in Data Flow and don't try map directly. Instead, we will try modify format of New1 and New2 to meet with old format of Old1 and Old2. And then you will not change database tables / store procedures.
Sorry, I could not create the example SSIS for you because I'm busy now. I have experienced with the same case in my project, so try with this component.
Thanks,
ScriptComponent-DataFlow.PNG
Sorry, I could not create the example SSIS for you because I'm busy now. I have experienced with the same case in my project, so try with this component.
Thanks,
ScriptComponent-DataFlow.PNG
ASKER
I' m afraid I still don't understand how I could use script component in order to modify format of New1 and New2 ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I didn't understand the solutions proposed by the expert and found a solution of my own
- You can use Component Script in Data Flow. Read line by line and try to map New1 to Old1 , New2 to Old2.
Thanks,