Unusual error with no leads on how to solve it.
I am running SQL Server 2008 on a virtual server with Windows Server 20008 R2.
On my previous SQL 2000 server, I accessed Excel data through Opendatasource. Apparently that no longer works. Most people suggest SSIS.
I created a SSIS package and added a Data Flow Task. I added a connection manager to the Excel Spreadsheet which has 10 or 12 columns. I connected to the correct tab on the spreadsheet. Looking at it in Table or view, I see all the columns. Changed Data Access mode to SQL Command and typed in
SELECT fvendno, fpartno, 'ODS' AS PullType
When I click on Parse Query the message says, "The SQL Statement was successfully parsed." I click OK.
Now, if I click Preview or Columns, the screen greys out. When I click any where on the screen I get a box that says "Microsoft Visual Studio 2008 has stopped working."
I've tested this several times with the same results. Removing the added string column makes no difference. There is no indication of what is causing the problem.