SSIS Package: Match by ID and move files from One to another two folders.

Question

SSIS Package: Match by ID and move files from One to another two folders.

kran

Old Hand

Points: 338
More actions
July 11, 2019 at 6:27 pm

#3662094
Hi - Need your help in this. Below is the scenario :
1. I have got of files with the format (Name_ID_PatientID_currenttimestamp.pdf), these files are generated daily. Highlighted yellow is the PatientID in the file. these files are in Folder A
2. I also save these filenames and Patient ID in a SQL table.
3. Now, I want to Match with the Patient ID in SQL table with the Patient ID (Name_ID_PatientID_currenttimestamp.pdf) in Folder A and move these files to Folder B and Folder C.
How do i do that ? Is it possible ? Thank you in advance
- This topic was modified 5 years, 4 months ago by kran.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

kran wrote:

Hi - Need your help in this. Below is the scenario : 1. I have got of files with the format (Name_ID_PatientID_currenttimestamp.pdf), these files are generated daily. Highlighted yellow is the PatientID in the file. these files are in Folder A 2. I also save these filenames and Patient ID in a SQL table. 3. Now, I want to Match with the Patient ID in SQL table with the Patient ID (Name_ID_PatientID_currenttimestamp.pdf) in Folder A and move these files to Folder B and Folder C. How do i do that ? Is it possible ? Thank you in advance

"I also save these filenames and Patient ID in a SQL table" --1
"Now, I want to Match with the Patient ID in SQL table with the Patient ID" --2

Doesn't the table from statement (1) provide this match for you?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kran Old Hand Points: 338 More actions · Answer 2

I want to move those matched PDF files with Patient ID from Folder A to Folder B and Folder C.

This reply was modified 5 years, 4 months ago by kran.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Is this the required process? If not, what is?

For each file
Extract the patient Id from the file name
Check whether the extracted patient Id matches a row in the SQL Server lookup table
If a match is found
Copy the file to folder B
Copy the file to folder C
Delete the file from folder A
End If
Next file

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kran Old Hand Points: 338 More actions · Answer 4

For each file

Extract the patient Id from the file name

Check whether the extracted patient Id matches a row in the SQL Server lookup table

If a match is found

move the file to folder B

move the file to folder C

End If

Next file

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

kran wrote:

For each file Extract the patient Id from the file name Check whether the extracted patient Id matches a row in the SQL Server lookup table If a match is found move the file to folder B move the file to folder C End If Next file

OK, so you need to use a FOREACH container to iterate round the files in the folder.

Use an Expression to extract the Patient Id from the file name and store in a package-scoped variable.

Use an ExecuteSQL task to check for the value in the lookup table, using the stored variable. Return 1 for found, 0 for not found (for example).

Use File System Tasks or a C# Script Task to copy the files, if 1 was returned (use a Precedence Constraint to control whether this gets executed).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin