Exporting Database for Importing on Another Server

This may be a simple question with an obvious answer, but I'm fairly new to databases and I can't seem to find the right procedure.

I created a database on my machine. If I want to copy the information from this database (columns, tables, and entries specifically) from my machine to another SQL server on a different machine, what is the best way to do this without manually re-entering all of the information?

Is there a simple export/import of a database for transferring it from one server to another?

Thanks,

~Josh Graber

Using Backup and Restore is often considered the 'best' way to move a database from one server to another.

Check in Books Online about using Restore with the [with MOVE] option.

|||

there are many method which u can use for this purpose....

(a) Backup/Restore

(b) Detach /attach

(c) copy database wizard

u can read about these method in BOL ...

Madhu

Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

I defined a Flat File Destination (using a flat file connection manager). When setting up the flat file connection manager, I am asked for a file... Does this mean one should create manually a template file with the desired output format? So I used a production file as template since we're replacing an existing process.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

When I create the file connection manager, there's no way to mention the row delimiter. In the properties I see a "Row Delimiter" field and when I try with "{CR}{LF}" it makes no difference. Interesting to note that, contrary to the HeaderRowDelimiter field, the RowDelimiter field has no drop-down control to give choices.

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:

Thanks I'll try that!!

EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

i had probs with a fixed width export, as well -- and the probs didn't seem "rational"; i just couldn't get the fixed-width SSIS solution to work. as soon as i setup up the ragged right solution, everything fell into place.

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

sql

Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

I defined a Flat File Destination (using a flat file connection manager). When setting up the flat file connection manager, I am asked for a file... Does this mean one should create manually a template file with the desired output format? So I used a production file as template since we're replacing an existing process.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

When I create the file connection manager, there's no way to mention the row delimiter. In the properties I see a "Row Delimiter" field and when I try with "{CR}{LF}" it makes no difference. Interesting to note that, contrary to the HeaderRowDelimiter field, the RowDelimiter field has no drop-down control to give choices.

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:

Thanks I'll try that!!

EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

i had probs with a fixed width export, as well -- and the probs didn't seem "rational"; i just couldn't get the fixed-width SSIS solution to work. as soon as i setup up the ragged right solution, everything fell into place.

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

Exporting a table and importing it

I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:

> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/e...reate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/e...design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/e...pt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/e...pt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>

Exporting a table and importing it

I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:

> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/e...reate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/e...design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/e...pt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/e...pt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>sql

Exporting a table and importing it

I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
Karen
Are the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegro ups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:

> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en...eate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en...esign_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en...t_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en...t_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegro ups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>

Exporting a table and importing it

I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
--
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>

Exporting & Importing certain tables

We have a database which services a number of websites. We want to move
individual tables for each website from the exisiting server to the new SQL
2005 Cluster as we modify the websites to point to the new location.
Is this possible or do I need to restore the whole database?
Thanks,
Neil Paddock.SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
move individual tables from one database/server to another. See BOL for
details.
TheSQLGuru
President
Indicium Resources, Inc.
"Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> We have a database which services a number of websites. We want to move
> individual tables for each website from the exisiting server to the new
> SQL
> 2005 Cluster as we modify the websites to point to the new location.
> Is this possible or do I need to restore the whole database?
> Thanks,
> Neil Paddock.
>|||Thanks for the info.
I have attempted to copy a table from one database to another using
Import/Export Wizard but I'm receiving an error message:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only
column "Employee_id".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination -
employee_details" (130)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems to be a validation error to do with the destination being read
only? How do I change this?
Thanks,
Neil Paddock.
"TheSQLGuru" wrote:

> SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easil
y
> move individual tables from one database/server to another. See BOL for
> details.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
> news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
>
>sql

Exporting & Importing certain tables

We have a database which services a number of websites. We want to move
individual tables for each website from the exisiting server to the new SQL
2005 Cluster as we modify the websites to point to the new location.
Is this possible or do I need to restore the whole database?
Thanks,
Neil Paddock.
SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
move individual tables from one database/server to another. See BOL for
details.
TheSQLGuru
President
Indicium Resources, Inc.
"Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> We have a database which services a number of websites. We want to move
> individual tables for each website from the exisiting server to the new
> SQL
> 2005 Cluster as we modify the websites to point to the new location.
> Is this possible or do I need to restore the whole database?
> Thanks,
> Neil Paddock.
>
|||Thanks for the info.
I have attempted to copy a table from one database to another using
Import/Export Wizard but I'm receiving an error message:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only
column "Employee_id".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination -
employee_details" (130)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems to be a validation error to do with the destination being read
only? How do I change this?
Thanks,
Neil Paddock.
"TheSQLGuru" wrote:

> SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
> move individual tables from one database/server to another. See BOL for
> details.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
> news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
>
>

Exporting & Importing certain tables

We have a database which services a number of websites. We want to move
individual tables for each website from the exisiting server to the new SQL
2005 Cluster as we modify the websites to point to the new location.
Is this possible or do I need to restore the whole database?
Thanks,
Neil Paddock.SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
move individual tables from one database/server to another. See BOL for
details.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> We have a database which services a number of websites. We want to move
> individual tables for each website from the exisiting server to the new
> SQL
> 2005 Cluster as we modify the websites to point to the new location.
> Is this possible or do I need to restore the whole database?
> Thanks,
> Neil Paddock.
>|||Thanks for the info.
I have attempted to copy a table from one database to another using
Import/Export Wizard but I'm receiving an error message:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only
column "Employee_id".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination -
employee_details" (130)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems to be a validation error to do with the destination being read
only? How do I change this?
Thanks,
Neil Paddock.
"TheSQLGuru" wrote:
> SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
> move individual tables from one database/server to another. See BOL for
> details.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
> news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> > We have a database which services a number of websites. We want to move
> > individual tables for each website from the exisiting server to the new
> > SQL
> > 2005 Cluster as we modify the websites to point to the new location.
> >
> > Is this possible or do I need to restore the whole database?
> >
> > Thanks,
> > Neil Paddock.
> >
> >
>
>

Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.

Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.