Post reply

ssis foreach loop

  • November 25, 2008 at 1:59 am

    #196814

    Hi all,

    I am a MS SQL DBA, i want to make one package for inserting data from flat file to the sql server table, but the files i'd like to put in the folder in lokal disc and to make a foreach loop to search within the folder to insert the data from flat file and then to copy or move that file after inserting. Can anyone help me for this.

    Thank's all.

  • November 25, 2008 at 6:54 am

    #903102

    Not sure what the problem is?

    Sounds like you want to interate through a list of flat files and put that data into tables.

    If so setup a file connection with a non specific name like "FileToProcess". Then rename one of the files as FileToProcess and setup a data flow using that file connection.

    Next set up a for each loop - Using for each file enumerator

    Inside the loop use a file system task to rename the file to "FileToProcess"

    then execute the data flow.

    When the data flow is done use another file system task to either Delete, Move, or Rename the file back.

    The agent account needs access to all file locations or Better setup a credential and create a proxy to exectue the job as, that has access to all file locations and databases.

    Hope that helps

  • November 25, 2008 at 7:08 am

    #903110

    Thanks,

    I have done in FTP, but i'd like to make it in the local disk, so i don't know how to write the path for variable in flat file connection with foreach loop enumrator, e.g

    "T:\\FTProot\\_DWfiles\\" +REPLACE( REPLACE( @[User::diskFileName] ,"\\", ""), ":", "") + ".TMP"

    to put for add procesing.

    How to write the path for local drive.

  • November 26, 2008 at 6:57 am

    #903688

    Jamie Thompson has a clear example of this.

    Benjamin Lotter
    http://BenjaminLotter.info/[/url]
    http://www.LinkedIn.com/in/BenjaminLotter
    Delight thyself also in the LORD and He shall give thee the desires of thine heart.
    ~Psalm 37:4

  • November 26, 2008 at 12:11 pm

    #903977

    Use file system task... to get the list of files and loop for each file.. you can get search for the code which will fetches list of files available in the folder ... then you can use the for each loop and you can import the data from csv file.

  • December 2, 2008 at 1:14 am

    #906063

    Thank's to all.

    I solved my problem using File System Task. :w00t:

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

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