CODEWITHSUNDEEP
c#sql-serverssissql-server-2014smartsheet-api
Danrex
Danrex

Reputation: 1655

Creating excel sheet and entering into SQL won't work when run together?

So I have some code that creates an excel worksheet from Smartsheets and then extracts that information from the excel worksheet into SQL. Thing is the code works if I run each part seperately but if I run it at the same time it will create the table, but it won't input the data. Below I will show each part in the main program. I don't think it is necessary to show the rest of the code because as I say it works seperately. 

//Make an Excel sheet from smartsheet
        Smartsheet smartsheet = new Smartsheet();
        long excelSmartsheetID = smartsheet.getSmartSheetID(currentWorkSheet);
        smartsheet.createExcel(excelSmartsheetID);

 //Extract Data From Excel into SQL
        SSIS excelToSQL = new SSIS();
        excelToSQL.storeSmartSheetDataToSQL();

So I am not sure what is going on here. I have put the thread to sleep for 10 seconds in between the two sections but it still doesn't work. Completely lost as to what might be the problem. I should add I am using SSIS to connect to the excel sheet and create it in SQL. Please let me know if you require more information.

Upvotes: 1

Views: 379

Answers (1)

Brett
Brett

Reputation: 2823

It sounds like the Excel file is still open when trying to access it a second time. When an Excel file is open for writing it becomes locked which can prevent another process (in your case SSIS) from editing the file. This can be confirmed using the Smartsheet C# SDK with code like the following which never closes the file after writing to it.

// Set the Access Token
Token token = new Token();
token.AccessToken = "YOUR_TOKEN";

// Use the Smartsheet Builder to create a Smartsheet
SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
BinaryWriter output = new BinaryWriter(new FileStream("c:\\file.xls", FileMode.Create));
smartsheet.Sheets().GetSheetAsExcel(8325033727682436L, output);
Console.WriteLine("Done writting");
System.Threading.Thread.Sleep(100000);

Run the above code and after it opens and writes to the file it will sleep for a very long time. While the code is sleeping you can try to manually open the Excel file and you will get a dialog like the following showing that we still have the Excel file open (from our code) even though we finished writing to it.

enter image description here

The solution to this issue is to close the Excel file as soon as we are done writing to it. This can be accomplished by using the close() method on the stream or with the using statement. I prefer the using statement so an example of that is below:

// Set the Access Token
Token token = new Token();
token.AccessToken = "YOUR_TOKEN";

// Use the Smartsheet Builder to create a Smartsheet
SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
using (BinaryWriter output = new BinaryWriter(new FileStream("c:\\file.xls", FileMode.Create)))
{
    smartsheet.Sheets().GetSheetAsExcel(8325033727682436L, output);
}
Console.WriteLine("Done writting");
System.Threading.Thread.Sleep(100000);

Now if we run the above code it will sleep at the end but this time the Excel file will not be locked since the using statement closed the file as soon as we finished writing to it.

Upvotes: 3

Related Questions