Hi All, I am trying to Process an Excel file using SSIS 2005 package which is in .xls format and which doesn't work with me when I open the file and save it it gets saved in .xlsx format and it works with my package . now i am trying to add a step in my package for this . I got code in different language that do this and it is saved in a note pad with extension as .ps1 (I have no clue what that is ). I tried calling this in the package using the Execute Process task and it executes but do nothing . can some one help me how to use this in SSIS package Please! Thanks in advance. $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault $excel = New-Object -ComObject excel.application $excel.DisplayAlerts = $False $excel.visible = $true $folderpath = "D:\Test\Test*.xls" ------I have Created more test files with different Dates so I gave * $folderpathnew = "D:\Test" $filetype ="*xls" Get-ChildItem -Path $folderpath -Include $filetype | ForEach-Object{ $path = ($_.fullname).substring(0,($_.FullName).lastindexOf(".")) "Converting $path to $filetype..." $workbook = $excel.workbooks.open($_.fullname) $workbook.saveas($path+".xlsx", $xlFixedFormat) $workbook.close() } $excel.Quit() $excel = $null [gc]::collect() [gc]::WaitForPendingFinalizers()

First of all you are talking about different versions of MS Excel, the SSIS version is MS Office agnostic, you need to have the correct Jet drivers to process the right version of the Excel. Based on what I see your machine has a newer version of Excel in which you can still save this file (Save As command) as an olded version (e.g. Excel 2003). Then check if when the package when it will be on the server can process the file. The code you got is PowerShell which is probably not installed on your machine.Arthur My Blog

Thanks For your Responce Arthurz! Well I am using the OPENROWSET in a Sp to process the file . and I am calling the SP in a Package .For This the files should be in .xlsx is there any other way that I can make it possible. all I have to do is automate the process of open a .xls file and re-save it as .xlsx (Not just renaming the file ). If I could add it as a step before the SP start processing I should be good. Please suggest me . Thanks!

..I am using the OPENROWSET in a Sp to process the file . and I am calling the SP in a Package .For This the files should be in .xlsx is there any other way that I can make it possible. all I have to do is automate the process of open a .xls file and re-save it as .xlsx (Not just renaming the file ). If I could add it as a step before the SP start processing I should be good. Please suggest me . I suggest you do it using the Excel connection, I see no reason using the Openrowset when you are in SSIS. How to load an Excel file using SSIS: http://goo.gl/4nSSsArthur My Blog

Hi Arthurz, Thanks for the blog. In my try at the excel source editor when i select the file it throw me an error saying "the file is not in the expected Format" so I switched back to OPENROWSET . If I re save it manually and give the file it works . so before I give it to the Excel Source I need to resave it in .xlsx format . and then continue with the procedure given in the blog http://goo.gl/4nSSs. Thanks

You are welcome, and then yes, this boils down to you not having the exact driver for Excel. See below how to fine tune this and the above article must be the blueprint for you to build your solution: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64585/Arthur My Blog

I really appreciate your quick responses. well it didn't work for me.I tried exactly the way it showed me but no use. any more suggestions!

I guess then the other possibility is it is an incorrectly formatted Excel file.Arthur My Blog

I came to know that the file is originally a 2007 file and the file has been saved and sent in old format. so That is the reason I want to open the file and re-save it . while re-saving the file it gets converted to 2007 which I have on my machine.

Do you have any further solution for my scenario??