1

I have multiple flat files as my source. Each files are having some common columns. But there are also some extra columns in one of them. I need to move all the data from flat files to a table having the structure with just the common columns and skip the extra columns in the one of the files. Structure examples below:

Structure of File 1:

id, name, age

Structure of File 2:

id, name, age, address, country

Structure of File 3:

id, name, age, address

Structure of Table:

id, name, age

I want to populate the table for only the three common columns between the files. Rest I need to ignore. How can I achieve this using SSIS?

Improve this question
5
  • How many flat files? Commented Apr 18, 2016 at 11:37
  • that may vary. there is no fixed number of files. sometimes it may be just one and sometimes it can be 6 or 7 or 10.... it can be 'n' number of files Commented Apr 19, 2016 at 2:48
  • Is common columns across files known or unknown? Commented Apr 19, 2016 at 11:15
  • they are known... but the spellings maybe differ... although the positions remain the same Commented Apr 20, 2016 at 5:32
  • I am facing a similar challenge. The only difference is my destination table has all these columns (like a super set of all the columns). And my requirement is to populate whatever's available for every file and leave other columns Null in destination. Has anyone done dynamic mapping to a common destination table? Commented Apr 14, 2017 at 14:53

3 Answers 3

Reset to default
2

You will need to separate different type of files in different folder (you can do that in SSIS using file system task by matching file names etc, or in powershell). Files in each folder must have the same number of columns and name/type. Then you create for each loop for each folder and iterate through to get the data from required columns and load them into the destination table. You may need a final step (execute sql ) to remove duplication in the destination table.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Easy. Set up 3 DataFlow tasks, each one connecting to a different file. Each one writes to the same table (e.g. through an OLEDB Destination). For all except File1 (which matches the table structure), simply don't map the extra, unwanted columns (i.e. address,country) in the OLEDB Destination. The values in these columns will just get discarded, and not go into the table.

Improve this answer

1 Comment

the above three files was just for illustration. the number of files may vary everytime
0

You need a for each loop to iterate through the folder where the flat files are.

As long as the columns you want are in the same position in each file then all should be good.

Have a look here.

Improve this answer

7 Comments

I've tried that... It's not working.... in the file which has extra fields it's taking the data of those fields as one since the row delimiter is defined as CRLF.
What do you mean the "data of those fields"
say it considers the first 9 fields into the corresponding 9 fields in to the table... but from the file which has extra columns, it will take the data from 10th, 11th and 12th field into the 10th column.
Several options - but maybe the easiest one is to have a derived column after you have done the import and trim the final column? Is it a fixed length? Is there a delimiter you can work with?
If it's concatenating multiple columns into the 10th column, surely that doesn't matter. You're only importing the first 9 columns, so anything after that gets thrown away.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.