My Visual Studio crashed and I didn’t know why!

Rayis Imayev, 2014-01-31

Recently I had to develop a simple ETL task with the help of SSIS to transfer an Excel data file into a SQL Server table for reporting. It was nothing special, simple file and only a fraction of it needed to be transfer to I had use a SQL command to make reference to a required Excel range. However a later change request to add an extra column for the dataset being transferred made me repeating a mistake that I’ve already made before a few years ago, but I totally forgot about that experience. And in a addition to that my Visual Studio 2008 crashed with no error messages.

I tried to replicate this test scenario using VS 2012 with a little bit more luck but still with almost no error messages to investigate after the crash.

So here is a sample Excel file:

And let’s say, initially I needed to load only Column1 and Column2 data. So my data source in the data flow SSIS task with the SQL command

SELECT Column1, Column2 FROM [SheetData$B5:C10]

looked like this:

This worked well really well, until a customer requested to add a reference to the 3rd column of the file into the scope of the data transfer and I easily made this adjustment in the SQL command:

SELECT Column1, Column2, Column3 FROM [SheetData$B5:C10]

And right after pressing OK button in the dialog window the whole Visual Studio crashed with no warning message. So that’s why I didn’t know why it was happening .

After reopening the SSIS package, turning it to the [Offline Work] mode, changing the SQL command with the reference for the Column3 and then executing the package I was able to get more details for the original error:
in VS2008 it was “The external metadata column collection is out of synchronization with the data source
in VS2012 it was “Complete metadata information for the data source is not available

Refreshing metadata on the datasource didn’t help, my VS kept crashing on this simple step of adding Column3 into the SQL command of the datasource task.

And a bit later I realized that the whole problem was in the SQL command itself, particularly in the FROM clause of it. If you remember the Excel file table and the original range reference was made for 2 columns using this statement: [SheetData$B5:C10]. However Column3 was out of scope of this range and my final SQL command should have been written this way:

SELECT Column1, Column2, Column3 FROM [SheetData$B5:D10]

Then instantly I realized that I had already made similar mistake of incorrect Excel range specification in the FROM clause; and I had to make it again in order to learn it this time. So I hope by creating this first post of my blog I will remember this mistake even better 🙂

Enjoy your day and happy data adventures!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads