CODEWITHSUNDEEP
ssis
Debbie Dippenaar
Debbie Dippenaar

Reputation: 513

SSIS - Load flat files, save file names to SQL Table

I have a complex task that I need to complete. It worked well before since there was only one file but this is now changing. Each file has one long row that is first bulk inserted into a staging table. From here I'm supposed to save the file name into another table and then insert the the broken up parts of the staging table data. This is not the problem. We might have just one file or even multiple files to load at once. What needs to happen is this:

  1. The first SSIS task is a script task that does some checks. The second task prepares the file list.
  2. The staging table is truncated.
  3. The third task is currently a Foreach loop container task that uses the files from the file list and processes it:
    • File is loaded into table using Bulk Insert task.
    • The file name needs to be passed as a variable to the next process. This was done with a C# task before but it is now a bit more complex since there could be more than one file and each file name needs to be saved separately.
    • The last task is a SQL task that executes a stored procedure with the file name as input variable.

My problem is that before it was only one file. This was easy enough. What would the best way be to go about it now?

Upvotes: 0

Views: 5586

Answers (3)

Stoleg
Stoleg

Reputation: 9300

  1. In Data Flow Task which imports your file create a derrived column. Populate it with system variable value of filename. Load filename into the same table.

  2. Use a Execute SQL task to retrieve distinc list of filenames into a recordset (Object type variable).

  3. Use For Each Loop container to loop through the recordset. Place your code inside the container. Code will recieve filename from the loop as a value of a variable and process the file.

  4. Use Execute SQL task in For Each Loop container to call SP. Pass filename as a parameter like:

    Exec sp_MyCode param1, param2, ?

Where ? will pass filename INPUT as a string

EDIT

To make Flat File Connection to pick up the file specified by a variable - use Connection String property of the Flat File Connection

  1. Select FF Connection, right click and select Properties

  2. Click on empty field for Expressions and then click ellipsis that appears. With Expressions you can define every property of the object listed there using variables. Many objects in SSIS can have Expressions specified.

  3. Add an Expression, select Connection String Property and define an expression with absolute path to the file (just to be on a safe side, it can be a UNC path too).

Upvotes: 1

Nick.Mc
Nick.Mc

Reputation: 19184

Add a colunm to your staging table - FileName Capture the filename in a SSIS Variable (using expressions) then run something like this each loop:

UPDATE StagingTable SET FileName=? WHERE FileName IS NULL

Why are you messing about with C#? From your description it's totally unnecessary.

Upvotes: 0

S.A
S.A

Reputation: 196

All the above can be accomplished using C# code in the script task itself. You can loop through all the files one by one and for each file : 1. Bulk Copy the data to the staging 2. Insert the filename to the other table

You can modify the logic as per your requirement and desired execution flow.

Upvotes: 0

Related Questions