I have an SSIS package which download flat files and open and save one of them as Excel file. Then it extracts records from Excel to the database. Everything is done in one ssis package.
The reason why I chose to save the file as Excel format is because I was not able to get data reliably from the flat files.. After I did a lot of research and testing, I chose Excel as a way to do it.
I stored SSIS package as File System. Whenever I run the package manually, it works fine. But when I schedule to run it via SQL Job agent, it always fails. and the failing part is a script which opens the downloaded flat file and save it as Excel file.
I even set up an admin proxy to do this but no use.
I finally managed to get the error message (see below). I wonder if the error is due to 'The file is being used by another program'. Like I said, it works ok if I run manually. The file is definitely there...
I am not sure where to go from here. Any suggestions? What to test to confirm what... or... I don't know ..
ah... any guide where I can go from here??
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'G:\xxx\xxx\xxxxx\xxxxxxxxxxxxxxxx.csv'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
- Edited by SQLMa Saturday, October 26, 2013 12:06 PM
Please check the following.
1. The admin proxy account that you have access to the path to read & write.
2. Make sure that excel is not open.
3. Try implementing a logic to either drop the excel and recreate one, or replace the existing one, or create versions of excel.
Thank you for the reply..
1. admin proxy account is the local administrator account. FTP files are downloaded on the server itself therefore this should not be an issue.
2. No it's not
3. Please see the current script below. Any improvement I can make? (I only use C# for VSTO develpment not VB. I got an sample code from online and didn't bother to convert to C#)
Public Sub Main()
Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim FiletoOpen As String
' Dim ws As Microsoft.Office.Interop.Excel.Worksheet
FiletoOpen = Dts.Variables("User::FTPAUFileDestinationPath").Value.ToString + "\" + Dts.Variables("User::FTPFileAUDownloaded").Value.ToString
wb = excel.Workbooks.Open(FiletoOpen)
'' ws = wb.Worksheets(1)
Dts.TaskResult = ScriptResults.Success