SSIS , Load from a Flat File by a job that run daily and Column are changed anytime
Hi
i need to create a job for load from flat file to sql, job is ok i can do it. But flat files that i get, have some columns changeable.
For example, there are 4 static columns but some days, there can be additional columns.
And order is like this (with a example) : static column are : col1,col2,col3,col4 that ok after days i see there are col1,col10,col11,col2,col3,col4,col5 then my job failed because i dont have that columns (col10,col11 and col5) in my table in sql. because
i created my table according to static columns.
How can i load them by a job SSIS.It is so urgent please help me.
July 13th, 2011 10:17am
I would do this in a script component using as source.
There you can overread the columns you don't have in your table.
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 10:34am
One approach I like is to get this file in as one record. You pretend in the Flat File manager it has one column.
Then you can parse it and determine how many columns are in there today and direct the package to one DFT or another that treats a file with col1,col2,col3,col4or the other that can process col1,col10,col11,col2,col3,col4,col5Arthur My Blog
July 13th, 2011 10:35am
I think I found you a workable example:
http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 10:38am
I think I found you a workable example:
http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/
Arthur My Blog
In this example, he know, need 3 columns but i dont know how many columns i need, it is not static and it changeable anytime.
First day i got a flat file with 4 column but today i got with 7 column in the flat file.Also i created my table accroding file of first day i got(with 4 column). I altered my table and added 3 columns more. but maybe i will get 9 column next day and my
job again will failed, and i have to do it manually always?
But i have many files like this. What must i do?
July 13th, 2011 11:20am
I would do this in a script component using as source.
There you can overread the columns you don't have in your table.
But i need old datas that in my table, i need keep datas that i got before.
How can i do as you said? Maybe i didnt understand you. Can you explain more please?
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 11:24am
One approach I like is to get this file in as one record. You pretend in the Flat File manager it has one column.
Then you can parse it and determine how many columns are in there today and direct the package to one DFT or another that treats a file with col1,col2,col3,col4or the other that can process col1,col10,col11,col2,col3,col4,col5
Arthur My Blog
Until now, files that i got, i saw all columns are same in same file, ex: iif today file has 4 column,it is for all data in the this file, and if other days file has 7 columns, it is for all data that in this file. i can do in one record. But i have a
table which in the replication.I cant create a new table for every file. I have to load it to same table. Also what is DFT?
July 13th, 2011 11:27am
You do not need to create the table, you can use default or even NULL value for those columns that are missing to insert in Data Flow Transformation (DFT explained). You just need [perhaps] two of them: one insert a set of 4 and another a set of 7 columns.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 11:51am
but i dont know how many columns will i get, sometimes 4 sometimes 7 and maybe 10 other day. Every day can change columns, also i dont know about column name. Just i have information this note they said me,. read note plz
Note: The data contained in these files comes sorted alphabetically, but the receiving program must read the header row to determine which column contains which demographic field. Since demographic fields can be added at any time, the
file format is inherently dynamic.
July 13th, 2011 11:55am
i don't think there are about 4 - 7 columns. it could be more. that's why he/she said 'changeable'
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 11:57am
Then it becomes even more complex. I think then you are better off creating such a package programmatically once you know how many column arrived (IMHO this request to ETL this file is silly).
How to create a package programmatically:
see http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html or
http://ssisbi.com/building-ssis-packages-programmatically-part-1/Arthur My Blog
July 13th, 2011 12:15pm
Already i have a package for loading. I need make a dynamic package
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 3:18pm