none
Please walk me through setting up a package with a For Each File Container to import flat files to a table using package configurations.

    Question

  • I am just not getting it - how to loop through flat files and get them into a database table. I can do it in the BIDS environment, but I cannot get them to work consistently using package configurations, especially when the end user wants to change the source folder, etc. I have the following sample situation:

    Folder: (on my workstation): C:\MyFolder\

    MyFiles: MyFile1.txt, MyFile2.txt, MyFile3.txt, MyFile4.txt. The contents, at this point doesn't matter.

    All I want to do is import them all to MyTable and then create a package configuration file to allow the user to change the foldername primarily.

    I don't know what values I need in the configuration file. Right now, I have the login and password to the database, the package password, and a variable I created called strFilePath with a value in the configuraion file of C:\MyFolder\MyFile1.txt. (but I don't know how to configure that either)

    Either I get no files found, or 1 file loaded, or some kind of error. I have spent many hours trying everything. Often I think it is working and it is only importing the files from my desktop instead of what is in the configuration file. When I get it to use the configuration file somehow, I end up with either nothing, 1 file, or an error.

    If someone can walk me through

    1) Creating the simple package

    2) Creating the right variables for the source folder and files and the correct default values for each.

    3) Adding the For Each Loop container and configuring the Folder and Files field in the Collection tab and the Expressions field at top, if necessary.

    4) Creating the proper file mask to allow it to get all 4 of those files, 1, 2, 3, 4 respectively

    5) Create a package configuration that will allow the user to change the folder, but not necessarily the file mask

    6) Properly configuring the source connection manager primarily

    7) Anything else vital that I didn't think of.

    Any help here would be greatly appreciated. I know and have found how to do most of this. I am just having a disconnect someplace and can't get it.


    • Edited by duanewilson Tuesday, December 11, 2012 3:47 PM Added one more point.
    Tuesday, December 11, 2012 3:44 PM

Answers

  • I want to say learning is the key, please start with the basics as Understanding Integration Services Package Configurations

    Now to the specifics: the configuration needs to point the ForEach Loop to the correct folder, and a file mask needs to be used, which is the case then sure, you have to provide both.

    The double slashes mean you work in a share, so ensure it is correct and accessible to the account running the package.

    In short, I suggest you adopt the approach depicted here: http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx


    Arthur My Blog

    • Marked as answer by Eileen Zhao Tuesday, December 18, 2012 6:31 AM
    Tuesday, December 11, 2012 4:33 PM
    Moderator

All replies

  • Looping thru files is one aspect, setting it up so a configuration is used yet another,

    let's look 1st at how one would configure the looping (and I trust you did something similar and it is working)

    http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

    Now because you tell me the package just does not pick the needed amount or breaks makes me think we need to concentrate on the case where it pick just one because this is as close as you could be successful.

    So I guess let's get into the stage where this works, and then review your config and also add logging to inspect whether you get any warnings.

    Report back on the above and what you see in the config, in the target directory etc.


    Arthur My Blog

    Tuesday, December 11, 2012 3:52 PM
    Moderator
  • Thank you for the reply.

    I am not even sure which situation is which as I have changed it so much.

    I think my configuration file worked with one file when I had one in the configuration file, like this:

        <Configuration ConfiguredType="Property" Path="\Package.Variables[User::strFilePath].Properties[Value]" ValueType="String">
            <ConfiguredValue>C:\MyFolder\MyFile1.txt</ConfiguredValue>
        </Configuration>

    I am not sure if you are supposed to put one filename here, or the mask value, like MyFile*.txt, or no file name at all and let the mask take care of it. I also heard there is a necessity in the folder name for double slashes (\\) for escape purposes, but I don't know where to put them and where not to put them, either in the folder value in the ForEach Loop container, the variable, or the configuration file.

    Tuesday, December 11, 2012 4:02 PM
  • My suggestion to you is to remove (delete) the currently set in place configuration and re-do this piece.

    Arthur My Blog

    Tuesday, December 11, 2012 4:11 PM
    Moderator
  • I appreciate your help here, but maybe this is where I am going wrong. What do I configure? There are so many settings. Do I configure the folder only, Or the filename AND folder? Or do I configure a whole connection string with the folder and filename (1 of the files in it or the MyFile*.txt syntax. And is this where the double slashes go, and if not, where do they go?
    Tuesday, December 11, 2012 4:21 PM
  • I want to say learning is the key, please start with the basics as Understanding Integration Services Package Configurations

    Now to the specifics: the configuration needs to point the ForEach Loop to the correct folder, and a file mask needs to be used, which is the case then sure, you have to provide both.

    The double slashes mean you work in a share, so ensure it is correct and accessible to the account running the package.

    In short, I suggest you adopt the approach depicted here: http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx


    Arthur My Blog

    • Marked as answer by Eileen Zhao Tuesday, December 18, 2012 6:31 AM
    Tuesday, December 11, 2012 4:33 PM
    Moderator