INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SSIS Update Table with flat file source and variables

SSIS Update Table with flat file source and variables

(OP)
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

RE: SSIS Update Table with flat file source and variables

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: How to get the best answers before you ask a question

RE: SSIS Update Table with flat file source and variables

(OP)
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

RE: SSIS Update Table with flat file source and variables

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: How to get the best answers before you ask a question

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close