Importing data into SQL Server 2005 via ODBC

Hi

I've got an Ingres database of some 200 tables which I need to import
every night into SQL Server 2005 for use by Reporting Services. Most
of the tables will come across unchanged (a few need massaging to
handle time intervals correctly), but the Import Wizard only seems to
want to import one table (or more accurately query) at a time. I seem
to remember the old 2000 Import Wizard handled multiple tables - is
there any way of processing multiple tables in 2005, or must I resign
myself to writing 200 import packages in SSIS.

Chloe Crowder
The British Library

Re: Importing data into SQL Server 2005 via ODBC

Chloe C (chloe@mcrowdd.plus.com) writes:
> I've got an Ingres database of some 200 tables which I need to import
> every night into SQL Server 2005 for use by Reporting Services. Most
> of the tables will come across unchanged (a few need massaging to
> handle time intervals correctly), but the Import Wizard only seems to
> want to import one table (or more accurately query) at a time. I seem
> to remember the old 2000 Import Wizard handled multiple tables - is
> there any way of processing multiple tables in 2005, or must I resign
> myself to writing 200 import packages in SSIS.

Are you looking at the Import Wizard in Mgmt Studio? I think you should
look into SQL Server Integration Services, for which you can create
packages in Business Intelligence Development Studio. (Although that is
as much I know about SSIS.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Importing data into SQL Server 2005 via ODBC

Hi Chloe,

Just had to do this today and it seems to work fine (although importing
multiple Excel sheets to multiple tables). After you select the Source and
Destination, there is a screen that asks to "Copy data from one or more
tables/views" or "Query" (not sure about the exact wording of the options
but something close). Then you can do mapping to destination tables/columns.
The last step is to execute or save as SSIS package.

If you do not see that option, check if you have the latest service pack. It
will be worth testing with SQL Server as Source, just to see if it has to do
with the driver for the source (I am confident you can import multiple
tables when the source is SQL Server).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Importing data into SQL Server 2005 via ODBC

On Tue, 27 Nov 2007 19:13:10 -0500, "Plamen Ratchev"
wrote:

>Hi Chloe,
>
>Just had to do this today and it seems to work fine (although importing
>multiple Excel sheets to multiple tables). After you select the Source and
>Destination, there is a screen that asks to "Copy data from one or more
>tables/views" or "Query" (not sure about the exact wording of the options
>but something close). Then you can do mapping to destination tables/columns.
>The last step is to execute or save as SSIS package.
>
>If you do not see that option, check if you have the latest service pack. It
>will be worth testing with SQL Server as Source, just to see if it has to do
>with the driver for the source (I am confident you can import multiple
>tables when the source is SQL Server).
>
>HTH,
>
>Plamen Ratchev
>http://www.SQLStudio.com
>

Plamen

thanks for this. Importing multiple tables works well from SQL Server,
but with the Ingres server as the source the Copy data from one or
more tables/view option is greyed out, leaving only the option of a
single select statement in a query.

Ah well, I'll do it the hard way.

Chloe

Re: Importing data into SQL Server 2005 via ODBC

On Tue, 27 Nov 2007 22:59:18 +0000 (UTC), Erland Sommarskog
wrote:

>Chloe C (chloe@mcrowdd.plus.com) writes:
>> I've got an Ingres database of some 200 tables which I need to import
>> every night into SQL Server 2005 for use by Reporting Services. Most
>> of the tables will come across unchanged (a few need massaging to
>> handle time intervals correctly), but the Import Wizard only seems to
>> want to import one table (or more accurately query) at a time. I seem
>> to remember the old 2000 Import Wizard handled multiple tables - is
>> there any way of processing multiple tables in 2005, or must I resign
>> myself to writing 200 import packages in SSIS.
>
>Are you looking at the Import Wizard in Mgmt Studio? I think you should
>look into SQL Server Integration Services, for which you can create
>packages in Business Intelligence Development Studio. (Although that is
>as much I know about SSIS.)

Erland

I was rather hoping to, in effect, automate the creation of my SSIS
package with the Import Wizard, otherwise I need to do each import as
a separate task in SSIS.

Ah well, do it the hard way...

Chloe