Post reply

Process Multiple File per Customer

  • June 28, 2011 at 9:15 pm

    #240054

    I have a Customer XYZ , it has two sources one is FTP and other is excel, FTP file has some reference data in Excel file , so whenever we load two files we do a merge join on Code which is common between two files and load it into a destination.

    @Data Flow level: FTP File 1 merge join on code with Excel file1

    The complexity comes when XYZ has multiple FTP files and multiple excel files; what should my approach be in order for me to load FTP File 1 with excel file 1 , FTP file 2 with Excel file 2 , FTP file 3 with Excel file 3 …. Or how can I relate FTP File 1 with excel file 1 , FTP file 2 with Excel file 2 , FTP file 3 with Excel file 3. I don’t want to process FTP file 1 with excel file 3 or FTP file 2 with excel file 1

    @Data Flow level: FTP File 1 merge join on code with Excel file1

    @data Flow level: FTP File 2 merge join on code with Excel file2

    @data Flow level: FTP File 3 merge join on code with Excel file3 and so on…..

    Please do let me know

    Thanks

  • June 29, 2011 at 1:03 am

    #1345675

    Do the Excel files and the FTP files have each time the same structure?

    If yes, you need to loop over these files using a for each loop and reuse the dataflow for each set of files.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 29, 2011 at 1:41 am

    #1345691

    How do you know that Excel file 1 and FTP file 1 belong together? Is there some sort of file-naming convention?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 29, 2011 at 6:51 am

    #1345880

    Hi Phil Parkin Thanks for Ur response , I was also thinking something like this , but how will naming convention help me ?

    lets suppose if I purpose this Solution to business:

    FTP file 1 name = XYZ_FTp_1 & Excel File 1 = XYZ_Excel_1

    FTP file 2 name = XYZ_FTp_2 & Excel File 2 = XYZ_Excel_2

    FTP file 3 name = XYZ_FTp_3 & Excel File 3 = XYZ_Excel_3

    Now how will it help in making sure that FTP 1 goes with excel 1 , ftp 2 with excel 2 so on and not FTP 1 with excel 2 from SSIS prospective

    Thanks

  • June 29, 2011 at 7:30 am

    #1345917

    Citiinvestor (6/29/2011)

    Hi Phil Parkin Thanks for Ur response , I was also thinking something like this , but how will naming convention help me ?

    lets suppose if I purpose this Solution to business:

    FTP file 1 name = XYZ_FTp_1 & Excel File 1 = XYZ_Excel_1

    FTP file 2 name = XYZ_FTp_2 & Excel File 2 = XYZ_Excel_2

    FTP file 3 name = XYZ_FTp_3 & Excel File 3 = XYZ_Excel_3

    Now how will it help in making sure that FTP 1 goes with excel 1 , ftp 2 with excel 2 so on and not FTP 1 with excel 2 from SSIS prospective

    Thanks

    This is not entry-level SSIS - I hope that you are ready for a challenge. Here is one way of doing it:

    1) Adopt a file naming convention, so that the files can be identified in pairs as you suggest (FTP1,Exc1), (FTP2, Exc2) etc etc.

    2) Write a Script task to check how many files are to be processed and assign this value to a SSIS package-scoped int variable. A value of 3 in the variable would indicate that there are three of each type of file to process (6 in total).

    3) After the Script task, drive a FOR loop with this variable.

    Inside the for loop you'll need a data flow task which does the imports.

    The data flow task will use source connections which have SSIS expressions for the file sources. The expressions will derive the file name based on the current value of the FOR loop counter.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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