Board index MS SQL Server

Access data pumps

Access data pumps

Post by Jonathan Randl » Thu, 26 Jun 2003 22:24:02



Hi,
I'm trying to establish how a data pump from a text file to an access
database works under the covers in a dts package.  The speed on an insert
for large files (1 million rows) is too good to suggest a simple line by
line insert statement via oledb.  Is there some sort of buld insert
operation performed or does it talk to a native access interface?  I would
like to replicate the same performance for the task outside of dts.

Thanks for any information that might shed some light on this subject.
Jonathan

 
 
 
Top

Access data pumps

Post by Darren Gree » Sat, 28 Jun 2003 06:42:48




Quote:>Hi,
>I'm trying to establish how a data pump from a text file to an access
>database works under the covers in a dts package.  The speed on an insert
>for large files (1 million rows) is too good to suggest a simple line by
>line insert statement via oledb.  Is there some sort of buld insert
>operation performed or does it talk to a native access interface?  I would
>like to replicate the same performance for the task outside of dts.

>Thanks for any information that might shed some light on this subject.
>Jonathan

 From what I can see it just uses the JET provider. If you create an
Access connection you can examine all the OLEDB properties through
disconnected edit. There certainly doesn't appear to be any magic going
on.

How DTS reads and transfers data between providers is what it is all
about, but at the end of the day it just uses the OLE-DB interfaces
itself. Any buffering or batching could be done by you in the same way.

Personally I wouldn't try and duplicate DTS, basically because I know I
could never even get close, especially since I don't know C++ which is
what you will need for performance and to directly access the OLEDB
providers. Why not use DTS but just wrap it in your own program. You can
build an entire package within a COM compliant language such as VB, and
execute it without ever opening Enterprise Manager or connecting to a
SQL Server, you just need the DTS libraries.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 
Top

Access data pumps

Post by Jonathan Randl » Sat, 28 Jun 2003 16:47:15


Cheers for that Darren, I'll give the DTS package via COM a go :)





> >Hi,
> >I'm trying to establish how a data pump from a text file to an access
> >database works under the covers in a dts package.  The speed on an insert
> >for large files (1 million rows) is too good to suggest a simple line by
> >line insert statement via oledb.  Is there some sort of buld insert
> >operation performed or does it talk to a native access interface?  I
would
> >like to replicate the same performance for the task outside of dts.

> >Thanks for any information that might shed some light on this subject.
> >Jonathan

>  From what I can see it just uses the JET provider. If you create an
> Access connection you can examine all the OLEDB properties through
> disconnected edit. There certainly doesn't appear to be any magic going
> on.

> How DTS reads and transfers data between providers is what it is all
> about, but at the end of the day it just uses the OLE-DB interfaces
> itself. Any buffering or batching could be done by you in the same way.

> Personally I wouldn't try and duplicate DTS, basically because I know I
> could never even get close, especially since I don't know C++ which is
> what you will need for performance and to directly access the OLEDB
> providers. Why not use DTS but just wrap it in your own program. You can
> build an entire package within a COM compliant language such as VB, and
> execute it without ever opening Enterprise Manager or connecting to a
> SQL Server, you just need the DTS libraries.

> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com

 
 
 
Top

1. Data Pump or Data-Driven Query?

I need to import data from a text dump to update a SQL Server table.
I've used DTS for simple data imports before, but I need to add some
logic to this import. Basically, i want to update the SQL table if a
record already exists for a given key, and insert if not.

Seems like this would be a basic function, but I can't find sample
code anywhere to do this.

I've tried adding a Data-Driven Query Task using the following Activex
transformation, but it fails on execution, giving no error (thanks,
MS):

'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************
Dim objConn
Dim objRS
Dim fld
Dim strSQL
Dim strConn

strConn = "*deleted*"

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

Function Main()

        ' --- set up the columns to transform
        DTSDestination("AIN") = DTSSource("Col001")
        DTSDestination("Associate_Name") = DTSSource("Col002")
        DTSDestination("Facility") = DTSSource("Col003")
        DTSDestination("UDN") = DTSSource("Col004")
        DTSDestination("On_Leave") = DTSSource("Col005")

         ' open ADO Connection to DB
        objConn.Open strConn

        strSQL = "Select AIN from OECBT_IMPORT where AIN='" &
DTSSource("Col001") & "'"
        objRS.Open strSQL, objConn

        If NOT(objRS.EOF) Then
        ' --- employee record exists, so update it
                Main = DTSTransformstat_UpdateQuery
        Else
        ' --- record doesn't exist, so insert it
                Main = DTSTransformstat_InsertQuery  
        End If

        ' --- clean up
        objRS.Close
        set objRS = Nothing
        objConn.Close
        set objConn = Nothing
End Function

Basically, this code does a lookup on the import table to see if a
record already exists. This helps me determine whether or not I do the
insert or update. Then in the Queries tab I set up the Insert/Update
SQL statements.

What am I doing wrong?

Thanks,
Stephen

2. Oracle Lite JDBC OutOfMemoryError

3. Data Driven Query & Multiphase Data Pump

4. Changing codepage of a DBF file

5. Data Pump Error in Data Driven Query task

6. Problems connecting to OLAP server using ASP.NET

7. DTS - error in query source for data pump

8. Tables column order

9. DTS package with 2 data pumps gets automation error in VBA

10. Row count from data pump task

11. DTS Data Pump Task cannot write to ODBC?

12. DTS Paramatised Data PUMPS - Help Please

13. Data Pump Task Status


All times are UTC
Board index