Import and export data

Question

Post reply

Import and export data

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

rs SSCrazy Points: 2819 More actions · Answer 1

So is there any way to copy objects from non sql server database to sql server database by program.

Thanks

philcart SSC-Forever Points: 47795 More actions · Answer 2

Not the would be anymore managable than DTS. Informix isn't one of the mainstream DBMS's that third-party developers provide a lot of native support for.

In the DTS package what data source are you using, 'Other (ODBC Data Source)', 'OLE DB provider for ODBC', or something else?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

rs SSCrazy Points: 2819 More actions · Answer 3

rs

SSCrazy

Points: 2819

November 24, 2003 at 3:59 pm

#483466

I am using 'Other (ODBC Data Source)'

philcart SSC-Forever Points: 47795 More actions · Answer 4

For the import from A to B, do you have any transformations, or is it a straight Datapump from one table to the other?

If not, create a staging table that you can import the data straight into. Then use stored procedures to modify the data as it goes into the final tables.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

David Burrows SSC Guru Points: 65142 More actions · Answer 5

I agree with phillcart's postings, I transfer 7GB (46 tables) daily from a 3rd party database using ODBC and DTS is the fastest method. I create one DTS package per table (makes life easier to rerun in case of failure) and scheduled a job to execute each package in turn.

Far away is close at hand in the images of elsewhere.
Anon.

rs SSCrazy Points: 2819 More actions · Answer 6

Phil, Ans to your question

For the import from A to B, do you have any transformations, or is it a straight Datapump from one table to the other?

Nothing just table to table

David,

How long it takes to transfer 7GB. Are you deleting before import data, if so what happens if the job failed after deleting the data. My intension is do in one transaction.

Thanks.

David Burrows SSC Guru Points: 65142 More actions · Answer 7

quote:
How long it takes to transfer 7GB. Are you deleting before import data, if so what happens if the job failed after deleting the data. My intension is do in one transaction.

3hrs. I check that the database table is available (select ... from ...), if OK I then truncate the destination table and then do the transfer. I do column to column data pump with occasional column ActiveX to correct erroneous data. If one of the transfers fails I rerun the DTS for the table in question.

Far away is close at hand in the images of elsewhere.
Anon.

covitz Old Hand Points: 322 More actions · Answer 8

For B to C server you can use the OPENROWSET by program although it may be slow. See BOL for OPENROWSET