CODEWITHSUNDEEP
sql-server-2008ssis
Tim Vavra
Tim Vavra

Reputation: 537

add parameter value to table at import SSIS

I am importing an Excel table from an ftp site to SQL using SSIS. The destination table is going to be used to calculate good and bad records based on another SQL database. Here is my problem. The Excel file is name RTW_032613_ABC_123.xls. This file name is a concatenation of a number of fields. I cannot recreate it based on the fields in the table, so I need to retain it and pass it to the new table in SQL. I have a parameter @FileName that I am using to loop through the files in the ftp folder. What I would like to do is either combine the import of data from the Excel file with the file name or insert the file name in each record after the import. I am calling the SSIS procedure from another stored procedure in SQL. I tried adding a SQL data flow task but I am not seeing where I add the insert statement on either the SQL Server Compact Destination or SQL Server Destination.

I am over my head with SSIS on this one. The key is that the parameter that I need is available in SSIS but I really need to get it passed on to my SQL table.

TIA

Upvotes: 0

Views: 1070

Answers (1)

Aphillippe
Aphillippe

Reputation: 655

If I'm reading your question right, you have an SSIS package with a variable containing the filename and you want to save the filename with each row that you are sending to your SQL table? If so:

  1. Add a derived column to the data flow, making a new column and referencing the variable in the expression
  2. Include that new column in the mapping for the destination of your data flow, sending the filename to whichever column you would like to save that data in.

No need for a seperate SQL task.

Upvotes: 2

Related Questions