CODEWITHSUNDEEP
ssis
Sam332
Sam332

Reputation: 67

How to load files with different names to different tables in SSIS?

I have two files that are named like this: CustomerReport(08022021-08032021) ComparingReport(08022021-08032021) I need to load the CustomerReport to a table and the ComparingReport to another table. I tried for each loop container but I cant think of how the expression will be to pickup the file. Im thinking of something like Customer*.csv where the * acts like a wild card but that didnt work. What can I do in this case?

Upvotes: 1

Views: 1398

Answers (1)

Marko Ivkovic
Marko Ivkovic

Reputation: 1288

Here key of the answer is to use Foreach Loop and Conditional Split.

Don't pay attention on errors, because I don't have your CSV files and tables in DB!

  1. Create a new variable FileName - string data type

enter image description here

  1. Add foreach loop and set like on screenshot

Collection Tab:

enter image description here

Variable Mappings Tab:

enter image description here

  1. Add Data Flow Task into Foreach Loop container

  2. Drag elements from toolbox like on a image

enter image description here

  1. Flat File Source connect to one of your CSV file using Flat File Connection Manager and on connection manager, in properties > expressions, set for ConnectionString a variable FileName

  2. Set Conditional Split like on a image

Expression for Customer is:

LEFT(
SUBSTRING(@[User::FileName],FINDSTRING((@[User::FileName]),"Customer",1),100),
FINDSTRING(SUBSTRING(@[User::FileName],FINDSTRING((@[User::FileName]),"Customer",1),100),"Report",1) - 1
) == "Customer"

enter image description here

  1. Connect Conditional Split to OLE DB Destination's


NOTE: I can't run package as I said on top of this answer, Pay attention to Conditional Split, but this is the way how you need to find if part of a string is into whole string.

Upvotes: 1

Related Questions