Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Loading flat files /adding filename to table column

I am trying to load a ton of flat files into a sql table and adding a filename column to the table.

in control flow I have 2 foreech loops grabbing the filenames and passing them to 2 variables.

this is where im having trouble in my data flow, I first have my flat file source pointing to derived column then pointing to OLE DB Destination.. in the OLE DB destination it is recognizing my filename derived column, but when I run the package it executes fine it just creates the table, it doesn't insert the flat file data or the filename from the 2 variables

here is what I have in my data flow tab

like image 221
user3772443 Avatar asked Oct 28 '22 21:10

user3772443


2 Answers

The flat file source has a special property for the purpose of giving you the file name:

  1. Right click on the flat file connection manager and go to advanced properties.
  2. In the component properties tab, go to Custom Properties> FileNameColumnName and enter a name, i.e. FileName
  3. Once you hit ok to save the changes, the FileName column will be added to the output buffer and this can be mapped in the data flow.
like image 82
Mark Wojciechowicz Avatar answered Nov 17 '22 10:11

Mark Wojciechowicz


Variable value changes cannot be read within the same DataFlow Task. You have to write an expression that get the Destination Table name in a Script Task or Expression Task inside the ForEach Loop container, then connect this task to the DataFlow Task. Note that you have to set the Delay Validation of the DataFlow Taskto True.

like image 27
Hadi Avatar answered Nov 17 '22 11:11

Hadi