Loading flat file using SSIS. File has fixed columns + can have any 6 out of a possible 60 columns

Question

Loading flat file using SSIS. File has fixed columns + can have any 6 out of a possible 60 columns

jaffarkazi

Grasshopper

Points: 14
More actions
July 7, 2010 at 3:22 am

#141021

I need to load a flat file [CSV] into a table using SSIS. The file has a partly fixed schema, and partly dynamic.
eg.
Name,Address,Age,SubX,SubY,SubZ,SubA,SubB,SubC
However, the records can have Sub values for the last columns as any of SubA to SubZ in any order.
So while 1 file's records has:
Name,Address,Age,SubA,SubC,SubZ,SubS,SubL,SubP
another file's records may have:
Name,Address,Age,SubZ,SubA,SubQ,SubR,SubT,SubM
Other than writing some scripting+parsing code to parse the dynamic columns into the appropriate columns, is there any other way to do this?
The database table will have the schema as:
Name,Address,Age,SubA,...,SubZ
EDIT: How useful would it be to put the remaining columns, and blank data per file?
eg.
In the case of Name,Address,Age,SubA,SubC,SubZ,SubS,SubL,SubP
what if I modify to make it
Name,Address,Age,SubA,SubC,SubZ,SubS,SubL,SubP,SubB,SubD,...
i.e., add all the remaining blank Sub columns?
Thanks and regards,
--Jaffar

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

Here is an approach you might explore - although I don't know efficient it would be with your volume of data. Also, this assumes that your Subxx records have a common data type - if not then this probably won't work.

1 - Create six variables to hold the dynamic field names

2 - Use a script task to read in the first row and set the variable values

3 - Use a data flow task to read the data into a staging table. Ignore the first row. The dynamic fields go into generic columns like Sub1, Sub2...Sub6

4 - Use an expression in a SQL task to construct an INSERT statement using the destination field names stored in the variables. Or alternatively, compose the SQL in a script task and store in a variable that you subsequently use in a SQL task to insert the records.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Ed Zann (7/7/2010)
Here is an approach you might explore - although I don't know efficient it would be with your volume of data. Also, this assumes that your Subxx records have a common data type - if not then this probably won't work.
1 - Create six variables to hold the dynamic field names
2 - Use a script task to read in the first row and set the variable values
3 - Use a data flow task to read the data into a staging table. Ignore the first row. The dynamic fields go into generic columns like Sub1, Sub2...Sub6
4 - Use an expression in a SQL task to construct an INSERT statement using the destination field names stored in the variables. Or alternatively, compose the SQL in a script task and store in a variable that you subsequently use in a SQL task to insert the records.

Or use a Script Component at item (3) to look after the dynamic redirection of data. But, as you say, common data types is a MUST for this approach.

jaffarkazi Grasshopper Points: 14 More actions · Answer 3

Thanks, Ed and Phil!

This looks like the right solution. And the data types are the same, so this is not an issue.

Regards,

--Jaffar

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It supports combination of static/dynamic data flows and doesn't require programming scripts.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/