Wednesday, June 6, 2012

SSIS Package : Export Data from Database, Daily to New Excel Without Using Script Task

Export data from database to excel sheet with new excel per day
SSIS => SQL Server Integration Services, is used for ETL (Extract, Transform and Load) type of work. It is advanced version of DTS we can say. Here we can schedule packages as jobs and it will execute without human intervention.
In this article we will export the data from SQL database table to excel sheet day wise.
To start with this you will need to install SQL Server 2008 with BIDS (Business Intelligence Development Studio)
Now moving forward the first step would be go to
File => New Project => Select “Business Intelligence Projects” from left panel and “Integration Service Project” from right panel. Give proper name and save at desired location.

On OK click, it will open Package.dtsx.
Create a Template folder somewhere on your hard drive. In that folder create a sample excel with just the headers that you want in the final excel.
We will use this template to create new excel every day with same format and new data.

Select File System Task from toolbox and drag on Control Flow Tab.
This task will be used to create excel every day before the actual code runs.

Change the name of task to if you want.
To change the properties of task, double click on it. Popup File System Task Editor will open up



Set the Source Connection to Template excel. Operation to Copy file (Which is set by default)
While setting the source connection do below things..
Click within the area in front of Source connection and select <new connection..>.
Below popup should be displayed.. 

Select Existing File there and click on browse, select the template excel over here


Click on ok.
Now let’s set the destination connection.
Set IsDestinationPathVariable to true.
And select the Destination variable



Change the name of variable if you want and click on Ok.
Set OverwriteDestination to true or false according to need
Now Select the Variable in left panel and press F4 to check the properties of it.



   
Set EvaluateExpression to true.. Now change the expression and set to below



Now go to Control Flow tab and double click on “Data Flow Task”.  It will switch to Data Flow Tab. Now check the toolbox it has been changed now.


It will contain three sections, source, destination and transformation. Now drag and drop the OLE DB Source, Data Conversion and Excel Destination to Data Flow Pane respectively.
And connect the green output of OLE DB Source to Data Conversion (Which will be useful for converting between non-unicode and unicode data) to Excel Destination as below



Now double click on OLEDB source and set the connection to whatever database you have.
Once this is done go to Columns section as below 


After that double click on excel destination and set the excel connection to Template excel for now.
.



Click on ok and do the modifications as in below figure. I’m selecting Data sheet of my Template excel workbook as it contains final columns that will exist in your excel workbook.


Once this is done, Click on mappings and do the mappings as necessary.



After source and destination settings are done. Right click on Data Conversion task and select edit Advanced Properties. And set the input output column names and data type to Unicode strings.  




Click on Ok. Data Conversion part is done.
Netx is again go to Excel Connection Manager, which is visible in below pane. Click on expression tab and map the Excel file path property to the variable that we created earlier as shown below


  



 

 
Select dataflow task and Set the delay validation to true, to avoid getting some errors at compile time related to excel sheet which may come.
Run the application now and package will copy template excel sheet from Template folder to destination folder with new name which will include today’s date. In the next date package will copy the data from database to excel sheet.

Happy Learning :)

2 comments:

  1. Got excel template copied but no data inserted to id

    See Debug:
    "SSIS package "Package.dtsx" starting.
    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
    Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
    Error: 0xC020801C at Data Flow Task, Excel Destination [22]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Destination" (22) failed validation and returned error code 0xC020801C.
    Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
    Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
    Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    SSIS package "Package.dtsx" finished: Failure."

    ReplyDelete