Developing Matt

My Technical Journal

Using SSIS to retrieve from ftp, and iterate through files

leave a comment »

SSIS is turning out to be a great tool.  It’s versatility in handling different scenarios leads me to believe it will be around a long time.

The current project is writing a package that pulls down all the files from an ftp site and loops through each one, processing each row in each file with lookups and updates in a way that would make T-SQL RBAR blush.

Here’s how I did it and the problems areas that I hit

1.  I set up an ftp task that downloaded all files.  This was surprisingly easy.  I set the remote parameter (to set what folder it looks for in the ftp site)…I set it as a variable so I could manipulate it later without much effort (using PACKAGE CONFIGURATIONS*)

New Picture

2.  I created a FOR EACH LOOP CONTAINER (using file enumerator) so that I could iterate through each file that I received from the ftp site.  There are a few steps for this to work.

    a.  Use variable Mappings to store the filenames

    b.  Don’t worry about the fact that you have to hard code the folder.  This can be changed dynamically through PACKAGE CONFIGURATIONS.*

   c.  Create a Flat File Connection to use the variable you set in step 2.a.  At first you have to just hard code in a valid file, but when done over-ride it:  look at the properties of your Flat File Connection object and click on the expression property.  From there set the ConnectionString value to your variable you set up in 2.a.  Thank you Allan Mitchell for your post about this. 

3.  Inside your FOREACH LOOP CONTAINER place your dataflow which will enable you to manipulate your data one file at a time.

4.  Inside your Data Flow Tab create a flat file source and point it to your Flat File Connection in 2.c.

5.  Finally I just used a file system task to archive it.

Using this approach I never have to know the names and all of the files and folders can be dynamically changed using Package Configurations.

 

*PACKAGE CONFIGURATIONS.  Use them. Right click on control flow area to get to it.  I stored all of my ftp host information, the ftp remote folder info, the local download directory, archive directory, and database destinations using package configurations.  All information is stored in a table so that I can easily change it later.  All of the ftp properties could be stored in this configuration table that I created except for the remote parameter…which is why I used a variable.

Advertisements

Written by matt

February 12, 2010 at 4:17 pm

Posted in SSIS

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: