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.
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
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.
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
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
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
Once the package is ready without errors we can run the package and it will turn green once it is successfully executed.
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