SSIS 2012 SSIS 2008 Moving data from a text file to excel sheet

SSIS 2008 or SSIS 2012 .Moving data from a text file to excel sheet

In this SSIS 2008 demo we are moving data from a text file to an excel sheet.
The target and Destination are having the same data structure.
This demo is done in Business Intelligence development studio and SSIS 2008 but it may also work in SSIS 2012 as the steps are the same.

Step 1:

Open Business Intelligence . Select File -New Project .From the new project window select SQL Server Integration Services Project and name it as MySSISProject and select the paramters as shown in the below screenshots

Step 2:

Double click on Package.dtsx Package which is inside SSISPackages folder in the solution explorer. From the Toolbox drag drop DataFlowTask on the Package.it is as shown in the below screenshot.

Step 3:

Double click on the DataFlowTask.Now drag drop an flat file source and excel destination as we are moving data from a text file into the excel sheet.
Follow the steps as shown in the below screenshot

Step 4:

Now Double click on the FlatFlat file source it will open a window.
from the window select new and then it will  open another window Name the Connection as EMPText and then browse your text file.
Once the text file is loaded it can be easily viewed in Columns,Advanced and Previewmode. Once all that is done just click ok.
Tick the Check box Column names in first line.

Once the FlatFile connection manager is done link the flatfileTask to ExcelDestination task
Now we have created our source next step is about the excel destination

Step 5:

Similarly double click on Excel Destination and follow the steps as shown below.

After following this steps you will find that there are two Connection Managers in the connection manager window at the bottom

Now if you see after doing the connection from the flatfile to excel it is showing an error like cannot convert between Unicode and non Unicode string datatypes

To fix this error we need to change the data type of Flatfile connection manager .
So double click on Flatfileconnection manager and change the datatype of all columns

Step 6:

Once the package is ready without errors we can run the package and it will turn green once it is successfully executed.

Conclusion :
After doing this SSIS demo you will be able to move data from an Flatfile source to Excel destination. This demo will work in SSIS 2008 and may also work in SSIS 2012.
In this example we have learned how to move flat file data to excel sheet.

If you have any queries or issues please leave a comment below