Here's my folder setup.
Here's the File setup
The idea is to traverse through the folders & put FileA contents to Table FileA.dbo on the database ( also FileB,FileC etc). The FileName structure is same throughout all the folders.
I have this ssis package where I parse through the folders with a foreachloop-> dataflow.
I have checked that the algorithm I've formulated to get the filename is working
REVERSE(Substring(Reverse( @[User::FileName] ),5,LEN( @[User::FileName] ))) == "FileA"
It parses out the .txt extension. Below is the setup of the foreach loop I have.
To start this whole package I've .txt files inside the folder & I make the foreach container loop subfolders.
Since, I'm not really a SSIS package developer, this is the best I could do with some research. The problem I'm running into is, it seems to work partially.
The snapshots are mockups of the scenario I have and in reality I have over 200 folders with 50 text files each designated to dump the contents to respective named tables.
But the total number of rows I'm seeing after the package successfully executes is extremely low & can't be right. Is there anyway to get a count/list of the number of folders it traversed. Also, am I doing something wrong ?
Ideally, I'd just like to not have to start with .txt files but just have the whole thing go to folders, get the filenames ( which I think I've a working code for) & just dump all the info to the OLEDB Destinations.
Any help, link to resources is much appreciated.
Solution overview
You can achieving this using one DataFlow Task inside the foreach loop, but the trick is that you have to Read source flat file name and the Destination SQL Table name from variables
Note: Flat files structure must be the same and SQL Tables must have the same structure
Detailed Solution
Control Flow
window and click onVariables
Declare 2 SSIS variables:
FlatFilename
: of typeString
and assign a default value a random file path (i.e.C:\MockFolder\FileA.txt
): of type
String` and assign to the following expression:This is assuming that all destination tables has the same schema
dbo
Foreach Loop Container
and aDataFlow Task
inside it, click on theDataFlow Task
and on the properties Tab, Set theDelay Validation
property toTrue
Foreach Loop container
and select the main Directory , and the files filter*.txt
also choose thefully qualified
retrieve file name option@[User::FlatFilename]
variableAdd 2 Connection manager
File
(i.e.C:\MockFolder\FileA.txt
)In The
DataFlow Task
, add aFlat File Source
and anOLEDB Destination
, in theOLEDB Destination
selectTable name from variable
option and select@[User::SQLTablename]
as the variable nameMap columns between source and Destination
Click on the
FlatFileConnection
in the connection manager windows, pressF4
to show the properties Tab, click on ExpressionsSelect the
Connection String
property assign to it the following expression: