DavidLumpkin
asked on
SSIS Package to Excel Makes Visual Studio stop working
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.
Any ideas?
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.
Any ideas?
ASKER
My spreadsheet only had 44 rows. There were some formulas in other columns, so I removed them and had the same results.
I then created a new spreadsheet and entered the following:
A1 fpartno
A2 fvendno
B1 001960
B2 9800-46000-1000
My first column here shows the location that I put the actual data (second column).
I saved this pristine spreadsheet and tried again. (I did check, as before, the first row contained headers).
Got the same results, SSIS restarts and my package did not save. I can't get past this.
I then created a new spreadsheet and entered the following:
A1 fpartno
A2 fvendno
B1 001960
B2 9800-46000-1000
My first column here shows the location that I put the actual data (second column).
I saved this pristine spreadsheet and tried again. (I did check, as before, the first row contained headers).
Got the same results, SSIS restarts and my package did not save. I can't get past this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So far this site has not helped much. No one can answer this question and I can't ask another because your site says I've abandoned it.
try this.
1)kept column name only 1 record in excel
2)create ssis package with 1 record excel
3)before execution put excel the original and check..
Thanks..