views:

641

answers:

3

I have a console app that uses two batch files to decompress a zip file, take the xls which was decompressed convert it to CSV and BCP the data into a SQL table. I'd like to schedule this console app to run once a day. I set up windows task scheduler to run said app. I try to "test run" the task and don't get an error. I have the app writing to a txt file when exceptions are caught and i get an error along the lines of "the xls file you are trying to use is being used by another process".

NOTE: I know there are more efficiant ways of accomplishing the importation of xls to SQL, but it is for a client who is too cheap to buy a FULL version of SQL SERVER.

+1  A: 

Something hasn't released it's hold on the XML file. You need to figure out what other program is using that resource and ensure that it's connections are closed. It could be that in your test run you don't close your connection, and then later on when the task is run, there's another process (the earlier instance of your app) that hasn't released the resource.

rie819
+1  A: 

sounds like you are triying to use the xml file before it's fully decompressed. Also check the possibility that rie819 mentioned before.

We could help you better if you post your scripts.

Jonathan
Imports SystemImports System.DiagnosticsImports Microsoft.Office.InteropImports System.Data.SqlClientImports System.Data.OleDbImports System.IOImports System.TextModule Module1 Dim currentDir As String = System.Environment.CurrentDirectory.ToString Sub main() callRFSRDecompressBatch() ExcelToCVS() callBCPCSVBatch() End Sub
Sub ExcelToCVS() Dim StrConn As String Dim DA As New OleDbDataAdapter Dim DS As New DataSet Dim Str As String Dim ColumnCount As Integer Dim OuterCount As Integer Dim InnerCount As Integer Dim RowCount As Integer Try StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\BCP\RFSR.XLS;Extended Properties=Excel 8.0;"
Dim objConn As New OleDbConnection(StrConn) Try objConn.Open() If objConn.State = ConnectionState.Closed Then Else End If Catch ex As Exception logError(ex) Exit Sub End Try Dim objCmd As New OleDbCommand("Select * from [rfsr$]", objConn) objCmd.CommandType = CommandType.TextDim ObjStreamWriter As StreamWriter ObjStreamWriter = New StreamWriter("c:\BCP\RFSR.CSV")
ObjStreamWriter.WriteLine(Str) Next ObjStreamWriter.Close() Try Catch ex As Exception logError(ex) End Try objCmd.Dispose() objCmd = Nothing objConn.Close() objConn.Dispose() objConn = Nothing Catch ex As Exception logError(ex) End Try End Sub Public Sub callBCPCSVBatch() TryDim targetDir As String Dim p As New Process p.StartInfo.CreateNoWindow = True targetDir = "C:\BCP\"
p.StartInfo.WorkingDirectory = targetDir p.StartInfo.FileName = "BCPCSV.bat" p.Start()p.WaitForExit()deleteXLS() Process.GetCurrentProcess.Kill() Public Sub callRFSRDecompressBatch() TryDim targetDir As String Dim p As New Process p.StartInfo.CreateNoWindow = True
targetDir = currentDir + "\Filez" p.StartInfo.WorkingDirectory = targetDir p.StartInfo.FileName = "RFSRDecompress.bat" p.Start() p.WaitForExit() Catch ex As Exception logError(ex) End Try End SubPublic Sub logError(ByVal ex) Dim SW As New StreamWriter("c:\BCP\BCPErrorLog.txt") SW.WriteLine(ex.ToString) SW.Close() End SubEnd Module
The code for the two DOS batch files are in the comments section of my question. Thanks for the help.
A: 

I found a way to produce the results I needed. I took the two batch files out of the console app. I now have a task scheduled to run one batch, the console app, and the last batch. Both answers I recieved were correct, it wasn't releasing the XML because of the console windows opening in the middle of execution. Since they have been removed I no longer have errors. Thanks for the help. -Z