Sample SSIS Package
Scenario:
In the inbound location, I have to create a directory which would be like “DealerSales and Current Date” and then download files from the remote location to that directory. Then I have to move the files from there to the working directory. Then I have to unzip the zip files and load the text files to the database and delete the text files from the working directory and move the zip from the working directory to the Archive folder. The directory’s date format should be in MMDDYYYY format.
Solution:
There could be a different approach to make the SSIS Package. One of the easier way to make the Package for this type of scenario could be like this:
- Let’s first have the File System Task which will create a directory like “DealerSales12052008″ inside download directory, if it doesn’t exist.
- Then let’s have the FTP Task which will download files from remote location to that folder.
- Then let’s have the File System Task which will create a directory like “DealerSales12052008″ inside the working directory, if it doesn’t exist.
- Then let’s have the For Each Loop Container and File System task which will move files from download directory to the working directory.
- Then let’s have the For Each Loop Container and Execute Process Task which will unzip the Zip files.
- Then let’s have the For Each Loop Container and Data Flow Task which will load all the text files into the SQL Server database.
- Then let’s have the File System Task which will create a directory like “DealerSales12052008″ inside the Archive Directory.
- Then let’s have the For Each Loop Container and File System Task which will move the zip files to the Archive Directory.
- Then let’s have the For Each Loop Container and File System Task which will delete the text files from the working directory.
- Then let’s have a File System tasks which will clean up our download directory and working directory.
So now let’s begin by making the skeleton of the package by dragging the task and which would look like this:
