· Introduction
Introduction Before going into the detail design of the package, let’s first see what this package basically do. This package will move all the contents present in the EXCEL file to the database. Once the data is moved from the file to Database it will be placed into to the Archived folder. It is not only applicable for the one EXCEL file it is also used for all the EXCEL file’s placed in that path.
Steps to create SSIS Package
Follow the below mentioned steps for creation of the new SSIS package.
1. First in the start menu choose Microsoft Business Intelligence Development Studio.
2. After selecting click on file at the top left corner of the screen and select new project or simply press (Ctl+Shift+N)
3. In that Choose “Integration Services Project” and give an most appropriate name for the package to be created. And press OK.
4. Now the screen will appear and it is ready for the package creation as shown below
Steps to create a Variable in the SSIS package
Follow the below mention steps for creation of the new variable in the SSIS package.
1. Right click on the Control Flow page and choose variables in the appeared menu.
2. Then the variables pan will appear as shown below 
3. Now click on the “Add” to add the new variables in the package. And we can give the appropriate data type and the Value that the variable holds.
4. Now for this package I have created 5 variables which hold the String data type and the values for the each variables are entered as shown below
Configuring the package
The created package will basically hold some connection string that need to be configured. Follow the below mentioned steps for configuring the package.
1. Right click on the screen and choose “New connection”.
2. In that various connection string will be listed as shown below. In these connections choose the most appropriate for your requirement. 
3. For this package I require EXCEL,and OLEDB connection manager which is shown in the above screen shot
4. After choosing the connection manager right click on the connection manager and configure the connection for the EXCEL and OLEDB as shown below:  
5. After configuring successfully press Ok button to save the configuration.
Components used in the package
1. DataFlow task is selected in the tool box and it is placed inside the content flow page. And the DataFlow task is renamed as” Loading the data from xcel to Database”.
2. Inside the dataflow task I have placed xcel connection manager and OLEDB destination are placed as shown below
3. Modifying the properties of the Excel source as shown below 
4. Now modifying the properties of the OLEDB destination and mapping its columns as shown below.
 
5. After completing all the steps press ok and return to the control flow page.
6. Now to move the file to the Archive folder I have chosed a file system task. Steps to configuring the file system task is mentioned below:
7. You can choose the operation whether you need to copy or move that property can be set in the operations pane.
8. In this we have given the source and destination as a variable, so that we can give those variables inside the file system task.
9. After all these steps establish connection between DataFlow task and the file system task.
10. If you have more than one xlsx file inside the folder we need to put it inside the foreach loop container. Steps to configuring the foreach loop is given below:
 
11. After completing all the above mentioned steps execute the package:
12. Package is executed without any issues as shown below:
Conclusion On the File System Task, the property OverwriteDestination was set to False (this is the default value). If you are moving files of same names to the Archive folder, you will get the error [File System Task] Error: An error occurred with the following error message: "Cannot create a file when that file already exists. ".To avoid this set the OverwriteDestination to True or the other option is to rename the files and copy it to Archive folder and then delete them.
Follow the above mentioned steps to successfully move the file from the local folder into the archive folder. |