Extract multiple files from multiple directories with a data flow task

  • Comments posted to this topic are about the item Extract multiple files from multiple directories with a data flow task

  • Good job, very well written and demonstrated. Awesome!

    Do you have something for connecting to a remote sever and downloading files? I think that's the next thing you should add to this.

    Thanks again, Super!

  • That's what this job actually does. I just used local directories for demonstration purposes.

    You would simply change the directory path from c:\folder\ to \\server\folder\, if I understand your question correctly.

  • Thanks For this article excellent explanation 🙂

  • Hi,

    You have done a very good demonstration.

    I did try what you have done in my visual studio 2005 but it doesn't work. It seems like the vb script code doesn't work well.

    After I have configurated your package (MultipleFileExtraction2005.dtsx) in my 2005 environment, when I run it just the "Get list of data files" (Script task) and the "Loop through list of data files" (ForEach Loop task) what are became green and the other tasks not

  • Sounds to me like it is not finding the data files. Make sure you have the paths set correctly, and files actually in the folders.

  • Yes you right, my data files were not into the correct location.

    Thanks 😉

  • 1. What kind of privs does the SSIS job need to be able to run the VB.Net scripts for this job?

    2. What kind of privs does someone need to run this job?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Great package and article. Thank you! I am saving this one 😉

  • It is the connection manager used by the SQL statement in the script task that determines whether the SQL statement can be executed.

    If your connection manager is using authentication that would allow you to execute the SQL Statement in SQL Server Management Studio, it will allow you to execute that same statement from an SSIS script task.

  • Stan Kulp-439977 (10/30/2011)


    It is the connection manager used by the SQL statement in the script task that determines whether the SQL statement can be executed.

    If your connection manager is using authentication that would allow you to execute the SQL Statement in SQL Server Management Studio, it will allow you to execute that same statement from an SSIS script task.

    Understood. But what level of privs does it actually need to work?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • What is your actual concern?

    Can you not get it to run?

    It would help if I knew what the problem was.

  • Nope... not having problems getting it to run because I haven't actually tried it. 😉 What I want to know is just exactly what I asked but I'll change the questions to a single question...

    You've built this very cool package. I'm a user. I want to run your package. What privs do I need as a user to run your package?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Are you going to be running it from Development Studio?

    How do you usually run your packages?

  • I hope my posting is not too late. I have 140 data file extracts being generated on a daily basis to load into SQL tables. Each data file has different number of columns and names, and so needs to be loaded into its own separate table.

    If the automated loading process fails to run overnight, I end up with 280 files in the folder the following day. If it doesn’t work over the weekend, I end with three days worth of data to load in on Monday morning after resolving the problem.

    I have modified Stan’s method (Extract multiple files from multiple directories with a data flow task) to suit my particular needs but when I run the package, I get the following error:

    Error: 0xC00220DE at Call SSIS package to load each data file: Error 0x80070003 while loading package file "". The system cannot find the path specified.

    What I want the package to do is load all data files into the tables, one by one. If there is more than one day worth of data files in the folder, I want the package to load them in date order. For example, if one set of data files (140) was generated two days ago, I want the package to load them in first, followed by those generated yesterday and lastly by those generated today. The dates on which the files were generated form part of the file names.

    I have added some steps to Stan’s SSIS package (see MultipleFileExtraction2008.dtsx). My package appears as follows:

    1) A master package named MFE_MASTER.dtxs, from which each mini package (Data Flow Task) is called to load a data file.

    2) An Execute SQL Task to create and populate a parameter table called tbl_ProgramParameter if doesn’t exist already.

    3) An Execute SQL Task to truncate the staging table. This is populated by a variable which passes the name of the staging table to the package.

    4) An Execute Package Task which calls the Data Flow Task (mini package) designed to load each data file. The expression tab of the Execute Package Task is configured to PackageName Parameter to a variable called @[User::PkgName]. The variable is defined in each Data Flow Task.

    5) An Execute SQL Task that calls a stored procedure to load the data into the staging table and transfer it to the storage table. The Parameter Mapping tab of the Execute SQL Task is populated with variables to pass the names of the staging and storage tables and a key field to the package. These variables are defined in each Data Flow Task (mini package).

    6) A file System Task to move files from the source to archive folder.

    Since it is failing at step (4) above, I have no way of knowing whether steps (5) and (6) will work or not. I have tried to resolve the problem several times but it just keeps throwing up the same error. I am a novice in creating SSIS package / Script Task, so am wondering if another variable needs to be defined in the package or something added to the script task. I would appreciate it if someone could help please and share the solution with me.

    The following information is attached in the zipped file:

    1) Sample data files to be loaded into the tables

    2) SQL script to create tables for loading the data

    3) SQL script to create a ProgramParameter table

    4) SQL script to create two stored procedures, one for truncating the staging table and the other for loading data.

    5) SSIS packages (includes a master package and six mini packages being called from the master package).

    Path where SSIS pacakages were created is E:\GEH_SSIS\MR5\MR5

    Thank you so much in advance for your help.

    Ray

    ray.abak@geh.nhs.uk

Viewing 15 posts - 1 through 15 (of 20 total)

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