2

I am building a simple SSIS project using Visual Studio which needs to copy a standardized column from hundreds of flat files into their respective rows into a SQL Server table.

All flat files have the same format and are placed into a folder alphabetically which is looped through and has one column copied over per file into the next available column of the SQL Server table.

The looping process follows this guide. The results are intended to work like the following:

file_1 
---------------------
col_1  col_2   col_3
-----  -----   -----
1      a       d
2      b       e
3      c       f

file_2 
---------------------
col_1  col_2   col_3
-----  -----   ------
1      g       j
2      h       k
3      i       l

Table_1
-------------------------------
col_1   file1_Col2   file2_col2
-----   ----------   ----------
 1         a             g
 2         b             h
 3         c             i

The issue arises in how to map the same column of each flat file to the next column of the SQL Server table. I am wondering if it is possible to create a string variable that could be used as a pointer to the next column or if there is a property I could adjust to achieve this effect?

Alex
  • 140
  • 6
  • 19

2 Answers2

1

You can do a Workaround to achieve this:

  1. In the foreach loop container add a dataflow task
  2. In the DataFlow Task add a Flat File Source, OLE DB Destination (as explained in the link you provided)
  3. Add a script component between the Source and Destination
  4. In the Script component you have to choose Column9 as Input
  5. In the Input and Output Tab add output columns in Output0 (you have to add all the column found in the destination table)
  6. In the ReadOnly Variables add the variable that contains the filename
  7. In the script component in the PreExecute Phase read the filename from the ssis variable and get the file number (i.e. if file2 then number =2))
  8. Dynamically assign the value to script output columns (follow my answer @ How can I loop though columns by name in an SSIS Script component?)
  9. Map all output columns to the related columns in the destination

Note: this will create multiple rows for each File:

Table_1
-------------------------------
col_1   file1_Col2   file2_col2
-----   ----------   ----------
1        a        
2        b        
3        c        
1                      g
2                      h
3                      i

So it needs that all data are imported to a staging table, then after all files are imported you have to insert data to the destination table:

First insert rows that contains only the col1 values, then update rows from the table where values are not null.

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

If you are using loop container to iterate over the files you can

  1. Set up variables with column names and last value using Expression Task
  2. Create a Data Flow and inside of it create ADO.Net Destination
  3. In Data Flow properties you can use expressions to pass your query - the property going to be called something like [Name Of Your Ado.Net].[SqlCommand] and value is going to be concatenations of your insert statement with your variables

I hope it is not too abstract

Ruslan Tolkachev
  • 634
  • 4
  • 9