Ravi Khanal

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:

1. Let’s first have the File System Task which will create a directory like “DealerSales12052008″ inside download directory, if it doesn’t exist.
2. Then let’s have the FTP Task which will download files from remote location to that folder.
3. Then let’s have the File System Task which will create a directory like “DealerSales12052008″ inside the working directory, if it doesn’t exist.
4. Then let’s have the For Each Loop Container and File System task which will move files from download directory to the working directory.
5. Then let’s have the For Each Loop Container and Execute Process Task which will unzip the Zip files.
6. 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.
7. Then let’s have the File System Task which will create a directory like “DealerSales12052008″ inside the Archive Directory.
8. Then let’s have the For Each Loop Container and File System Task which will move the zip files to the Archive Directory.
9. Then let’s have the For Each Loop Container and File System Task which will delete the text files from the working directory.
10. 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:

First of all lets make download, Archive, Workdir Directories inside the C:\SSIS folder. In the download directory we will download the files from remote location. Workdir is our working directory where we will copy the files from download directory. When the datas are inserted into the tables, then we will move the zip files to the Archive directory.

Now let’s feed our tasks. Let’s start by creating variables first.

Let’s create a Package level Variable called dname01 with String datatype and Value as C:\SSIS\download.

Let’s create another Package level Variable called dname02 with string datatype. Let’s make its EvaluateAsExpression True and on Expression let’s write as:

Now let’s create another Package level Variable dname03 of String Datatype. Let’s make its EvaluateAsExpression True with Expression as

Now let’s configure the File System task as:

Now we have created the directory called “DealerSales12052008″ inside the download directory. Now we are downloading the files inside this directory from the remote location using FTP task. In our Remote Location we have Some ZIP files starting with the name of DealerSales. So we can configure our FTP task as:

Now the zip files are already downloaded on the download directory. So now we have to move those files into working directory. So first of all we have to create a directory DealerSales12052008 inside the working directory. So lets create a package level variable dname04 with string datatype and expression as:

Then let’s configure the file system task as:

Now we have created the directory inside the working directory. So let’s move the files from download directory to the working directory by configuring the For Each Loop Container and File System task inside it.

Now let’s create a Package level variable fname01 of String datatype and Value any string like “dummy”.

Let’s configure For Each Loop Container as:

Now let’s configure the File System task as:

Now the files are inside the working directory so now we have to unzip all the files inside the directory. So now let’s create a Container level variable name01 of String datatype.

Now let’s configure the For each loop Container as:

Now let’s configure the Execute Process task as:

Now we have unzipped the files. So now we have to load those text files into the SQL server Database.

So let’s create a package level variable bname01 of string datatype with expression as:

Now let’s configure the For Each Loop Container for loading data into SQL Server as:

Now let’s configure the data Flow task as

Now the data is loaded from text file to the SQL Server. So now let’s create a folder in the Archive directory. For that we have to create a package level Variable dname05 of string datatype with expression as

Now we have to configure the File System Task for creating the folder inside Archive directory as:

Now we have to move those zip files into Archive directory. So lets create a Container level variable aname01 of string datatype with some string value like “dummy”.

Then we have to configure the for each loop container for moving zip files to Archive directory as

And we have to configure the file system task inside the Archive For Each Loop Container as

Now we have to delete the text files that have been extracted and loaded into SQL Server.

For that we have to create a Container Level variable tname01 of string datatype and some string value as “dummy”. Then we have to configure the for each loop container for deleting the text file as

Similarly we have to configure the file system task inside the Delete Text file for each loop container as:

So finally we have to configure our File System task to delete the contents from the download directory and the working directory as: