Results 1 to 4 of 4

Thread: DTS Error

  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unhappy Unanswered: DTS Error

    I need to copy some datas from Oracle to MS Access db. To do it, I use SQL Server Import and Export Data tool. But I want to move it to a Visual Basic program. I saved DTS package from Import and Export tool. I embedded to my VB program. But sometimes it gives me error like:

    XXX Step failed
    Microsoft Data Transformation Services (DTS) Data Pump
    The number of failing rows exceeds the maximum specified. (Microsoft JET Database Engine (80004005):
    Could not update; currently locked by user 'Admin' on machine 'X'.)

    How can I solve the problem?
    My connections strings are;

    For Oracle :
    Set oConnection = goPackage.Connections.New("OraOLEDB.Oracle")

    oConnection.ConnectionProperties("Persist Security Info") = False
    oConnection.ConnectionProperties("User ID") = user
    oConnection.ConnectionProperties("Data Source") = db
    oConnection.ConnectionProperties("Window Handle") = 0
    oConnection.ConnectionProperties("Locale Identifier") = 1055
    oConnection.ConnectionProperties("Prompt") = 2
    oConnection.ConnectionProperties("OLE DB Services") = -1

    oConnection.Name = "Connection 1"
    oConnection.ID = 1
    oConnection.Reusable = True
    oConnection.ConnectImmediate = False
    oConnection.DataSource = db
    oConnection.UserID = user
    oConnection.password = password
    oConnection.ConnectionTimeout = 60
    oConnection.UseTrustedConnection = False
    oConnection.UseDSL = False

    For Access:
    Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0 ")

    oConnection.ConnectionProperties("Data Source") = " & download_form.destination_text.text & "
    oConnection.ConnectionProperties("Mode") = 3


    oConnection.Name = "Connection 2"
    oConnection.ID = 2
    oConnection.Reusable = True
    oConnection.ConnectImmediate = False
    oConnection.DataSource = download_form.destination_text.Text
    oConnection.ConnectionTimeout = 160
    oConnection.UseTrustedConnection = False
    oConnection.UseDSL = False

  2. #2
    Join Date
    Jul 2003
    Posts
    37
    Depending on what version of SQL Server you are running you can create a graphical DTS package instead of just using the import export tool....

    I believe if you are running above SQL v. 7 it has the capabilites of the actual dts....

    Underneath your console root you should have access to the Data Transformation Services folder... when the node is opened, click once on local packages, then right click and design new package... When you create your dts package through this and use the transform data task from the oracle db connection to the access db, you are then going to want to save the package... You can save it in VB format. To do this under the Location drop down in the save box, select vb file and the directory where you want to save it...

  3. #3
    Join Date
    Jul 2003
    Posts
    2

    Question

    We use sql server 2000. I created package. I have no problem about creating package. I think the problem is parameters of connection or driver of access connection. Which parameter determines the max. rows which are inserted ?


    Originally posted by justastef
    Depending on what version of SQL Server you are running you can create a graphical DTS package instead of just using the import export tool....

    I believe if you are running above SQL v. 7 it has the capabilites of the actual dts....

    Underneath your console root you should have access to the Data Transformation Services folder... when the node is opened, click once on local packages, then right click and design new package... When you create your dts package through this and use the transform data task from the oracle db connection to the access db, you are then going to want to save the package... You can save it in VB format. To do this under the Location drop down in the save box, select vb file and the directory where you want to save it...

  4. #4
    Join Date
    Jul 2003
    Posts
    37
    I'm sorry I can't be more of help however, I did ask a friend for you and he suggested to try these few things

    Increase the connection timeout to a higher number (try 120 or above) and make sure you have the latest service pack installed for SQL Server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •