SSIS - Update error in For each loop

Question

I have some .txt files which I am trying to import to my oledb destination. I want to apply some updates to the data before extracting them in the .csv format. And am trying to achieve this through SSIS.

I have the below flow:

1>Execute sql task to truncate my existing table
2>Data flow task to import my flat file to the oledb dest
3>Execute sql task to update my data as per my needs.
4>Data flow task to export the data in csv file.

Concerns here is: I have many such text files and I want to use for each loop Container in my package, My text file has vendor wise data say eg: tibco.txt, I want to import this file from a location and extract it to destination folder with the same name but with .csv extension. I have used a variable that stores the name of the file each time the for loop runs and have set it in expressions : Connection String for the input flat file used in step 2 above.

My package runs fine until the 2nd step but fails to update the data at step 3.

Error: [Execute SQL Task] Error: Executing the query "update tablename set vendor_inventory = r..." failed with the following error: "Invalid column name 'HOSTNAME'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Does the tablename table have a HOSTNAME field in it? I think more details might help, like: are the text files all being imported into the same table or different tables? — mendosi
@mendosi yes, it has this field in it.the text files are imported in the same table one by one. — Ruchi

Vathsal Ravi Vathsal Ravi · Accepted Answer · 2016-11-17T15:19:26

I Suppose you are using a variable that you are changing somewhere in the Control flow and that is not reflecting properly to the expression.

I always use a Config table for such For each loops with file system

Table Defination

CREATE TABLE [dbo].[tbl_SSIS_InputMapping](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Conn_Mgr] [varchar](50) NULL,
    [MD_File] [varchar](500) NULL,
    [Input_Path] [varchar](500) NULL,
    [Output_Path] [varchar](500) NULL,
    [Archieve_Path] [varchar](500) NULL,
    [File_Format] [varchar](100) NULL,
    [File_Type] [varchar](50) NULL,
    [Package_Name] [varchar](100) NULL,
    [Active] [char](1) NULL,
    [AttachOutput] [char](1) NULL)

My Folder Structure looks like this

MainFolder\MetaData
MainFolder\Input
MainFolder\Output

My Config Table look like this

MetaDataFile is required for the validation of package. As you may move the input file after processing it. But the next time the package initiate , it will look for the file (which is moved/deleted) and it will fail.

This is a very generic approach which can be tailored as per your needs

SSIS - Update error in For each loop

1 Answers