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

  • kran

    SSC Veteran

    Points: 278

    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

    folder_A

    2. I also save these filenames and Patient ID in a SQL table.

    tableA

    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 days, 7 hours ago by  kran.
  • Phil Parkin

    SSC Guru

    Points: 243474

    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 folder_A 2. I also save these filenames and Patient ID in a SQL table. tableA 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?

     

     

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • kran

    SSC Veteran

    Points: 278

    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 days, 7 hours ago by  kran.
  • Phil Parkin

    SSC Guru

    Points: 243474

    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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • kran

    SSC Veteran

    Points: 278

    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: 243474

    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).

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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