October 28, 2016 at 8:20 am
One of the people I work with is trying to write an export (using the wizard) to pull data from 107 tables to an Excel spreadsheet that can then be burned to a cd/dvd and used to move data to another server on another isolated network (can't do a server to server transfer). We are getting an error about too many columns for one of the tables and the export fails.
What I don't understand is why SSIS is trying to create a destination table when the destination is an Excel spreadsheet. Please note, I used to write moderately complex SSIS packages at a previous employer, but I have always stayed away from Excel as a means of transferring data between systems.
My coworker is now looking at using Access for this purpose, which I think may work better but I'd like to understand what is going on with Excel.
October 28, 2016 at 8:33 am
Lynn Pettis (10/28/2016)
One of the people I work with is trying to write an export (using the wizard) to pull data from 107 tables to an Excel spreadsheet that can then be burned to a cd/dvd and used to move data to another server on another isolated network (can't do a server to server transfer). We are getting an error about too many columns for one of the tables and the export fails.What I don't understand is why SSIS is trying to create a destination table when the destination is an Excel spreadsheet. Please note, I used to write moderately complex SSIS packages at a previous employer, but I have always stayed away from Excel as a means of transferring data between systems.
My coworker is now looking at using Access for this purpose, which I think may work better but I'd like to understand what is going on with Excel.
How many columns are there?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 28, 2016 at 8:34 am
Phil Parkin (10/28/2016)
Lynn Pettis (10/28/2016)
One of the people I work with is trying to write an export (using the wizard) to pull data from 107 tables to an Excel spreadsheet that can then be burned to a cd/dvd and used to move data to another server on another isolated network (can't do a server to server transfer). We are getting an error about too many columns for one of the tables and the export fails.What I don't understand is why SSIS is trying to create a destination table when the destination is an Excel spreadsheet. Please note, I used to write moderately complex SSIS packages at a previous employer, but I have always stayed away from Excel as a means of transferring data between systems.
My coworker is now looking at using Access for this purpose, which I think may work better but I'd like to understand what is going on with Excel.
How many columns are there?
352.
October 28, 2016 at 8:46 am
If you look at the Excel connection string, is it using JET or ACE drivers?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 28, 2016 at 8:56 am
Why aren't you using flat files to transfer the data so you don't have to deal with additional storage needed by spreadsheets?
Also, depending on the driver, you can get a lower row limit when using Excel as a destination.
October 28, 2016 at 9:00 am
Phil Parkin (10/28/2016)
If you look at the Excel connection string, is it using JET or ACE drivers?
I just tested using 2013 ACE drivers and, even there, there is a 256-column limit.
Use a flat-file format instead, or Access as you've already suggested.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 28, 2016 at 9:28 am
Luis Cazares (10/28/2016)
Why aren't you using flat files to transfer the data so you don't have to deal with additional storage needed by spreadsheets?Also, depending on the driver, you can get a lower row limit when using Excel as a destination.
Call it lazy, he doesn't want to create 107 separate extracts, he just wanted to write one data transfer process. Due to the issues with using Excel he is going to try using Access.
October 28, 2016 at 9:40 am
Lynn Pettis (10/28/2016)
Luis Cazares (10/28/2016)
Why aren't you using flat files to transfer the data so you don't have to deal with additional storage needed by spreadsheets?Also, depending on the driver, you can get a lower row limit when using Excel as a destination.
Call it lazy, he doesn't want to create 107 separate extracts, he just wanted to write one data transfer process. Due to the issues with using Excel he is going to try using Access.
Weird, i would think that it's easier to output to flat files. I would generate bcp code with T-SQL by querying sys.tables and use a bat file to run it from the local machine (assuming they don't want to run it from the server). A similar option could be done for the import.
I guess everyone has their preferred methods. I just wanted to offer an option.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply