none
How to load multiple Text files in sql server table?

    Question

  • Hi, i have a parent and child package. It works when i load single text file in sql table but now i have multiple text files and i have to load them in the same sql table. I went through some of the forums but didnt understand well. All the text files are having same format. its just sometimes the number of text files might change. I tried using Foreach loop container but after executing package it loads same record 4 times. Can anybody help me or if you know any nice artical on this?
    Tuesday, June 21, 2011 3:20 PM

All replies

  • Take a look here: http://toddchitt.wordpress.com

    I think I cover using a For Each loop to iterate over files in a folder.

    Can you tell us how you set up your loop? Did you create a variable to hold the File Name? And are you setting the Connection String Property of the Flat File Connection Manager based on an Expression utilizing that Variable?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Tuesday, June 21, 2011 3:41 PM
  • Keep using the ForEach Loop, the issue seems in how you set the variable to capture the file name. I am not getting what you mean under the record, is it the file name or a record (row) data from a file?

    Just in case, a quality article on how to load multiple files with ForEach Loop: http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, June 21, 2011 3:43 PM
    Moderator
  • Thanks for your Todd, I just have data flow task in Foreach loop. in collection tab i have an expression for Directory : filelocation and For fileSpec. Underneath i am pointing it to folder path and Files : *.txt. i have a variable that holds the file name under variable mapping and i have a connectionstring property set for flat file showing (Filelocation+FileName).  
    Tuesday, June 21, 2011 3:51 PM
  • Try setting a break point on the Pre Execute event of the Data Flow task and watch the value of the FileName variable for each iteration of the loop.
    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Tuesday, June 21, 2011 3:55 PM
  • Hi, i took out the filelocation variable from the ConnectionString of Text file. it loops through the files and loads data but i am getting following error now. I am getting this error for couple of files.

     Error : System.IndexOutOfRangeException: Index was outside the bounds of the array.

    I am getting error at custom script component which i have a inside data flow task,

     

     



    Tuesday, June 21, 2011 4:40 PM
  • Here is an other example of a Foreach Loop Container.
    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Tuesday, June 21, 2011 5:39 PM
    Moderator
  • Hi, i took out the filelocation variable from the ConnectionString of Text file. it loops through the files and loads data but i am getting following error now. I am getting this error for couple of files.

     Error : System.IndexOutOfRangeException: Index was outside the bounds of the array.

    I am getting error at custom script component which i have a inside data flow task,

    Can you show us your code?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

    Tuesday, June 21, 2011 5:40 PM
    Moderator
  • Hi All, i can load all text files in sql now. It was just some text files were not having correct format. i just need little more help. after i am loading all text files, i am moving 'em to a different folder. I have following properties set in FileSystemTask.

    IsDestinationPathVariable - True
    DestinationVariable - Destination Variable.
    OverwriteDestination - Choice, I select True.

    Operation - Move File

    Source Connection -
    IsSourcePathVariable - False
    SourceConnection - Pick up the connection pointing to the source folder with one file selected.
    In the Connection Manager window right click on the new connection manager and select properties.
    Create a new expression -
    Property - ConnectionString
    Expression - FileName Variable

    I guess its all correct but it moves only one file and there are around 40 text files.


    • Edited by JoJo_1 Wednesday, June 22, 2011 1:49 PM
    Wednesday, June 22, 2011 1:36 PM
  • You need to set a wildcard to move all the files e.g. Myfile*.csv or if the file names are not falling into a pattern use a ForEach Loop to move them one by one (see http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html is need more info)
    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, June 22, 2011 1:48 PM
    Moderator
  • Hi ArthurZ, I couldnt see the pictures in the link in your comment but i put file system task in foreach loop and configured foreach loop same way i configured it for loading all text files and it worked. Its moving all files to different folder. Do you think its the correct way? just wanna make sure if it creates any error in future.
    Wednesday, June 22, 2011 3:36 PM