SSIS Update Table with flat file source and variables 1

[shaferda]

I am working on a package to simplify a task that has been running for several years. The current setup is one table that lists the database local to a division in our corporation, within these individual db's there is a table that lists the path for each flat file to be processed which relates to one machine. Within each directory specified by path there are multiple files to process.

I have to store the division and cell id's for each record processed out of the flat files. I'm thinking I can do this in SSIS by using a for loop to iterate divisions, an inner for loop to iterate the machines for that division then finally a for each loop to iterate the files within each directory. I think I can solve keeping track of the division & cell/machine by setting a variable for each of these properties in each trip through the respective loops. My question is if what I'm asking makes sense how do i make it a reality? Also, how can I insert not only the data from the flat file but also the current value of the variables set for division & machine?

Thanks

 

[xlbo]

Your theory sounds like it should work

1st loop query the table and return the database name

Set up the loop to go through database name and assign to variable "dbName"

2nd loop use dbName to create your connection string inside the loop and return the flat file locations

assign the flat file strings to another variable "ffLocation"

Use the ffLocatin to set the connection string for a flat file connection (can use expression for this).

Read in the data from teh flat file through a data flow task and use a derived column element to assign a column based on one or either of your variables

Question - do all the flat files have the same structure?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shaferda]

Thanks Geoff,

Now my only road block is converting the text file below to show the columns as rows. I've been agonizing over pivot & unpivot but can't seem to get any data into my destination table is the column names. Any ideas suggestions?

"Metric 1", "Metric 2", "Metric 3", "Metric 4"
1, 2, 3, 4

To show like

"Metric 1",1
"Metric 2",2
"Metric 3",3
"Metric 4",4

 

[xlbo]

I think to do something like that you will need to read each row of the flat file into a single column in a staging table and then work from there

Is there anything that identifies which row is which? and do you have more than 2 rows per file?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question