Sql server > SSIS >> Bussiness Intelligence Views : 1620
Rate This Article :

Excel Processing using SSIS

      ·         Introduction

·         Steps to create SSIS Package

·         Steps to create a variable in that package

·         Configuring the package
·         Components used in the package
·         Conclusion

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.

 

Create SSIS

  

4.      Now the screen will appear and it is ready for the package creation as shown below

Control Flow SSIS

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

       SSIS Creating Variables

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

Creating Variables SSIS

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”.

 

 5.jpg

  

2.      In that various connection string will be listed as shown below. In these connections choose the most appropriate for your requirement.

      

       6.jpg

   

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:

 

      7.jpg8.jpg

 

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

9.jpg

  

3.      Modifying the properties of the Excel source as shown below

 

       10.jpg

4.      Now modifying the properties of the OLEDB destination and mapping its columns as shown below.

              11.jpg 

 

              SSIS Column Mapping

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:

 13.jpg

 

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:

 14.jpg

15.jpg

   

11.  After completing all the above mentioned steps execute the package:

12.  Package is executed without any issues as shown below:

17.jpg  

 

18.jpg

 

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.

About Author
Venkatraman Natarajan
Total Posts 3
-
Comment this article
Name*
Email Address* (Will not be shown on this website.)
Comments*
Enter Image Text*
   
View All Comments
Kamalesh
Its very simple to understand. Good work
  Privacy   Terms Of Use   Advertise With Us   Contact Us
© 2016 Developerin.Net. All rights reserved.
Trademarks and Article Images mentioned in this site may belongs to Microsoft and other respective trademark owners.
Articles, Tutorials and all other content offered here is for educational purpose only and its author copyrights.