sql server - SSIS - Update error in For each loop -


enter image description herei have .txt files trying import oledb destination. want apply updates data before extracting them in .csv format. , trying achieve through ssis.

i have below flow:

1>execute sql task truncate existing table
2>data flow task import flat file oledb dest
3>execute sql task update data per needs.
4>data flow task export data in csv file.

concerns here is: have many such text files , want use for each loop container in package, text file has vendor wise data eg: tibco.txt, want import file location , extract destination folder same name .csv extension. have used variable stores name of file each time loop runs , have set in expressions : connection string input flat file used in step 2 above.

my package runs fine until 2nd step fails update data @ step 3.

error: [execute sql task] error: executing query "update tablename set vendor_inventory = r..." failed following error: "invalid column name 'hostname'.". possible failure reasons: problems query, "resultset" property not set correctly, parameters not set correctly, or connection not established correctly.

i suppose using variable changing somewhere in control flow , not reflecting expression.

i use config table such each loops 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 this

mainfolder\metadata mainfolder\input mainfolder\output 

my config table this

metadatafile required validation of package. may move input file after processing it. next time package initiate , file (which moved/deleted) , fail.

this generic approach can tailored per needs


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -