How to convert xls files to xlsx files and save in a folder using SSIS?

1
votes

How to convert .xls file to .xlsx file in ssis. My requirement is to export the user defined stored procedure in to excel destination. This is my control flow task:

[Execute SQL Task (Drop excel table)]-->[Execute SQL Task (create excel table)]-->[Data flow Task]

My Data flow task is :

[OLEDB Source (to execute the stored procedure)]-->[Data conversion(convert to unicode)]--> [Excel Destination]

When I do this, the excel destination table is stored as .xls file. I need to convert to .xlsx file. I have excel 2013 installed on my server but file saved as .xls. Please help me to write a code to convert xls files to xlsx file using SSIS.

Thank you for your time and help !

1
ssis
Why not change Excel Connection Manager by choosing Microsoft Excel 2007? In this way you can use an .xlsx file.. – ɐlǝx
I chose Microsoft Excel 2007. But is saving as .xls file – Anj
When you create excel table (Execute SQL Task) the name of the file is provided dinamically using Expression and variables? – ɐlǝx
I did not use expression and variables . In the first execute sql task, I dropped the table then I create the excel table again to ensure that the stored procedure result are not appending. I want to rewrite the excel table every time I execute the package. This is the link I referred dwhanalytics.wordpress.com/2011/04/07/… – Anj
Ok, just change your Excel Connection Manager to Microsoft Excel 2007 and pointing it to a real xlsx file (you can add before your table to this file manually using same name and same path but different extension). Now the two Execute Sql Task should recognise xlsx file. Create it and then verify mapping columns in Data Flow Task – ɐlǝx

1 Answers

1
votes

which version of data tool are you using? SSDT or BIDS?

If you are using SSDT, like Alex said, you can choose excel 2007 in the EXCEL destination and save the file as .xlsx. enter image description here

If you are using BIDS before SS 2005, you can try to use OLE DB destination and create a new connection manager through Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider. enter image description here