473,850 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 27 '07 #1
4 4934
Chloe C (ch***@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 27 '07 #2
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
Nov 28 '07 #3
On Tue, 27 Nov 2007 19:13:10 -0500, "Plamen Ratchev"
<Pl****@SQLStud io.comwrote:
>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
Nov 28 '07 #4
On Tue, 27 Nov 2007 22:59:18 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:
>Chloe C (ch***@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
Nov 28 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

32
3236
by: Neil Ginsberg | last post by:
We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC linked tables. I recently created a new set of tables for the app, and users are complaining that unsaved data is being lost when they move to a new record. This seems to be the case when there are multiple users. When there is a single user using it, we don't seem to have that problem. It seems that we had this problem when we first converted from an MDB back end...
11
3098
by: Skc | last post by:
I have a .txt which has been exported as a .csv from an external source. What i need to do is to import this into SQL2000 (into a table) but I need to do special things on the data: 1. I need to look for the first three chars and import rows into separate tables. E.g. if the first three chars begin with CCC, then this row goes into the CCC_table, if it is TTT then into the TTT_table etc... 2. Once I have my tables built up, I need to do...
3
2410
by: Mark Line | last post by:
Hello! I'm a python n00b! I've been writing in c++ for a few years so programming's not new to me, just python that I don't know the syntax!
11
2948
by: panic attack | last post by:
Hello everbody, Our system is using Sql Server 2000 on Windows XP / Windows 2000 We have a text file needs to be imported into Sql Server 2000 as a table. But we are facing a problem which is, Sql Server claims that it has a character size limit ( which is 8060 ) so it cant procceed the import operation if the text file has a record bigger then 8060. The records , in the text file, have a size bigger then 8060. So we wont be able to...
2
8197
by: Marcus | last post by:
(Appologies if this group isn't the best place for this post) Is it possible to use DAO 3.6 to access binary data (varbinary(max)) in Sql Server 2005? I have images and sound in a Sql 2005 DB that I need to retrieve (and write) with DAO (ADO and ADO.Net are not options as this is legacy code that can't be changed). Thx, Marcus
4
5632
by: Goofy | last post by:
The add database ( MDF ) in Visual Studio 2005 seems to be in SQL Server 2005 format as far as I can tell. Does anyone know how I can import tables ( without having to start writing queries ) from Access ? -- Goofy
12
6242
by: JMO | last post by:
I can import a csv file with no problem. I can also add columns to the datagrid upon import. I want to be able to start importing at the 3rd row. This will pick up the headers necessary for the datagrid. Once I can get to that point I need some way to be able to add new data only to the new columns that were added. Here is some of my code: //Function For Importing Data From CSV File public DataSet ConnectCSV(string filetable)
4
2297
by: Neil | last post by:
Is there a problem with stability when one uses too many text (memo) fields? I'm having a problem with data from one record occasionally ending up in another record, though apparently not through any user interaction. I'm using SQL 7 and accessing the tables through the ODBC driver. There are two tables with a one-to-one relationship -- TableA and TableB. TableB is the one that's having this occasional problem. TableB has about 30 text...
0
9895
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9744
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11011
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10352
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9503
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7900
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5735
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5931
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4547
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.