1

I have two CSV files with the almost same name.

1.S101053_345_1809.csv

2.SAS101053_345_1809.csv

Filename pattern is as below. 1. The First one is the sales file. "S" means Sales file,101053 is distributer code,345 is company code and 1809 is year and month. 2. the second one is Stock and Sales File. "SAS" means Stock and Sales,101053 is distributer code,345 is company code and 1809 is year and month.

I am trying to upload data through For each File loop container in SSIS. for SAS file package gets execute fine and files gets upload successfully. but when it iterates for S file (Sales File) it reads both files. Sales and Stock And Sales. because it finds "S" in starting in both files. attached is the screenshot of configuration for "For each loop container"

For each loop container configuration:

enter image description here

How to upload both files from the same folder.

keepAlive
  • 6,369
  • 5
  • 24
  • 39
Shahab Haidar
  • 625
  • 3
  • 11
  • 25
  • Try 2 foreach loops. The first looking for SAS*.csv and the 2nd looking for S*.csv This assumes you move the file to processed folder after load. – KeithL Oct 22 '18 at 12:22

1 Answers1

1

You could insert a script component to interrogate the filename. If it contains "SAS" then set an ignore flag variable. Then check for this and send the "SAS" files to a blank sequence container Data Flow

Script would be something like this

StrFileName = Dts.Variables("varFileName").Value.ToString 
'varfilename is the name returned from the for each loop
IntInstr = InStr(StrFileName, "SAS")
if IntInstr = 0 then 
   ' if "SAS" is not found in the filename then set the varFileOK to True
   Dts.Variables("varFileOK").Value = True
   Else
   Dts.Variables("varFileOK").Value = False
End If
Dts.TaskResult = Dts.Results.Success

Then use the "Expression and Constraint" "Edit" facility on the data flow arrow to test for the value of "VarFileOK".

If True then point data to the "File to ignore" data flow.

If False then process the file.

MiguelH
  • 1,415
  • 1
  • 18
  • 32
  • Hi, Thanks for your reply.. I am not use to C# codes. can you please give me whole c# script if it is possible for you.. thanks again. – Shahab Haidar Oct 24 '18 at 06:23
  • @ShahabHaidar unfortunately I only work in vb.net. It shouldn't be that difficult to convert this to c# though. See here: https://stackoverflow.com/questions/25112982/how-convert-vb-project-to-c-sharp-project – MiguelH Oct 24 '18 at 10:47