Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: Please help me! Problem with DTS application

    Hello everybody,

    I'm a new commer. I'm happy to join dbForum with all of you.

    I'm coding a DTS application for my company's data mart. And it's the first time I code a data movement application in .NET. I use SQL2000sp2 Personal Edition for datawarehouse and WinXPsp Professional.

    I have looked around the site http://www.sqldts.com/default.aspx?225 and regst.txt in the SQL-CD to reference DTS.dlls as below:
    INSTALLATION NOTES FOR DATA TRANSFORMATION SERVICES (DTS)
    ---------------------------------------------------------
    The following files must be registered using the regsvr32.exe utility:
    dtsffile.dll
    dtspkg.dll
    dtspump.dll
    axscphst.dll

    Example: regsvr32 80\Tools\Binn\dtsffile.dll

    The sqlunirl.dll file should reside in the system folder (i.e., \winnt\system32 or \windows\system).

    I added other dll references successfully but sqlunirl.dll. And I recieve the err:
    "C:\WINDOWS\system32\sqlunirl.dll" was loaded, but the DllRegisterServer entry point was not found. This file can not be registered."

    As I run my application, it also fire an unhandled exception like this:
    "Unable to cast COM object of type 'System.__ComObject' to Interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call the COM component for the Interface with IID
    '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT:0x80004002(E_NOINTERFACE))"

    And here is the Err details:

    See the end of this message for details on invoking
    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************
    System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
    at DTS.Task.get_CustomTask()
    at myDTSForNet.Form1.addDataPumpTask(String taskId, String taskName, String sql, String desTable) in E:\DATA\Kmy264\myDTSForNet\myDTSForNet\Form1.vb:li ne 81
    at myDTSForNet.Form1.cmdDoDTS_Click(Object sender, EventArgs e) in E:\DATA\Kmy264\myDTSForNet\myDTSForNet\Form1.vb:li ne 26
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


    ************** Loaded Assemblies **************
    mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
    ----------------------------------------
    myDTSForNet
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///E:/DATA/Kmy264/myDTSForNet/myDTSForNet/bin/Debug/myDTSForNet.exe
    ----------------------------------------
    Microsoft.VisualBasic
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualBasic/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll
    ----------------------------------------
    System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
    ----------------------------------------
    System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
    ----------------------------------------
    System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
    ----------------------------------------
    System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
    ----------------------------------------
    System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
    ----------------------------------------
    System.Runtime.Remoting
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Runtime.Remoting/2.0.0.0__b77a5c561934e089/System.Runtime.Remoting.dll
    ----------------------------------------
    Interop.DTS
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.0.0
    CodeBase: file:///E:/DATA/Kmy264/myDTSForNet/myDTSForNet/bin/Debug/Interop.DTS.DLL
    ----------------------------------------

    ************** JIT Debugging **************
    To enable just-in-time (JIT) debugging, the .config file for this
    application or computer (machine.config) must have the
    jitDebugging value set in the system.windows.forms section.
    The application must also be compiled with debugging
    enabled.

    For example:

    <configuration>
    <system.windows.forms jitDebugging="true" />
    </configuration>

    When JIT debugging is enabled, any unhandled exception
    will be sent to the JIT debugger registered on the computer
    rather than be handled by this dialog box.



    Please help me to solve the problem. Thanks alot.


    best reguard,


    khanhmy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You shouldn't have to be manually registering DLLs. It sounds to me like one of your software installs failed, and possibly you missed the error messages.

    Try reinstalling the software, and pay close attention to each confirmation message that pops up, even if you've already clicked "OK" twenty times in a row...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2006
    Posts
    4
    Thanks for your reply.

    I did re-install VS2005 and SQL2000 Personal Edition sp2. I don't know why I did not received any confirmation message during the installation.

    I also did add reference dtspkg.dll, dtspump.dll, custtask.dll into my project.
    To make it clear, I would like to send some code causing err to you.


    Imports DTS
    Public Class Form1
    Dim strCnn As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=(local)"
    'for package.sourceCnn.properties
    Dim serverName As String = "local"
    Dim userName As String = ""
    Dim passWord As String = ""
    Dim souCatalog As String = "pubs"
    Dim desCatalog As String = "test"

    'for pumpTask sample only
    Dim pumpTaskSql As String = "select * from employees"
    Dim pumpTaskDesTable As String = "dim_employee"

    Dim oPackage As DTS.Package


    Private Sub cmdDoDTS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDoDTS.Click
    'define package.properties for a new package
    oPackage = New DTS.Package
    createPackage(oPackage, "myFirstDTSApp", "Pump data from pubs to test.dim_employee")

    'define the connections for package
    addConnection("cnn_1", 1, serverName, "", "", souCatalog)
    addConnection("cnn_2", 2, serverName, "", "", desCatalog)
    'define the tasks for package
    addDataPumpTask("task_1", "pumpTask", pumpTaskSql, pumpTaskDesTable)
    'define the steps of package for tasks run
    addStepTask("step_1", oPackage.Tasks.Item("pumpTask").Name)
    'save the package
    oPackage.SaveToSQLServerAs("my.NET", "local")
    'execute the package
    oPackage.Execute()

    End Sub

    Private Sub createPackage(ByRef oPackage As DTS.Package2, ByVal name As String, ByVal des As String)
    With oPackage
    .Name = name
    .Description = des
    .WriteCompletionStatusToNTEventLog = True
    .FailOnError = False
    .PackagePriorityClass = DTS.DTSPackagePriorityClass.DTSPriorityClass_Norma l
    .MaxConcurrentSteps = 4 'Default = 4
    .LineageOptions = DTS.DTSLineageOptions.DTSLineage_None
    .UseTransaction = True
    .TransactionIsolationLevel = DTS.DTSIsolationLevel.DTSIsoLevel_ReadCommitted
    .AutoCommitTransaction = True
    .RepositoryMetadataOptions = DTS.DTSRepositoryMetadataOptions.DTSReposMetadata_ Default
    .UseOLEDBServiceComponents = True 'Default = TRUE
    End With

    End Sub

    Private Sub addConnection(ByVal name As String, ByVal id As Integer, ByVal serverName As String, ByVal userName As String, ByVal passWord As String, ByVal catalog As String)
    Dim oCnn As DTS.Connection
    oCnn = oPackage.Connections.New()
    With oCnn
    .Name = name
    .ID = id
    .DataSource = serverName
    .UserID = userName
    .Password = passWord
    .UseTrustedConnection = True
    .ConnectionTimeout = 30
    .Catalog = catalog
    End With
    oPackage.Connections.Add(oCnn)
    oCnn = Nothing
    End Sub

    Private Sub addDataPumpTask(ByVal taskId As String, ByVal taskName As String, ByVal sql As String, ByVal desTable As String)
    Dim oTask As DTS.Task
    Dim oPumpTask As DTS.DataPumpTask

    oTask = oPackage.Tasks.New("DTSDataPumpTask")
    oTask.Name = taskName
    oPumpTask = oTask.CustomTask
    With oPumpTask
    .SourceConnectionID = 1
    .SourceSQLStatement = sql
    .DestinationConnectionID = 2
    .DestinationObjectName = desTable
    End With
    addTransformationToTask(oPumpTask, "test.dim_employee_pumpTest.tran", "transform")
    oPackage.Tasks.Add(oTask)
    oTask = Nothing
    End Sub

    Private Sub addTransformationToTask(ByRef oPumpTask As DTS.DataPumpTask2, ByVal tranId As String, ByVal name As String)
    Dim oTransformation As DTS.Transformation
    oTransformation = oPumpTask.Transformations.New(tranId)
    With oTransformation
    .Name = name
    .TransformFlags = 512 'DTSTransformFlag_AllowLosslessConversion
    End With
    oPumpTask.Transformations.Add(oTransformation)
    End Sub

    Private Sub addStepTask(ByVal name As String, ByVal taskName As String)
    Dim oStep As DTS.Step
    oStep = oPackage.Steps.[New]
    With oStep
    .Name = name
    .TaskName = taskName
    End With
    oPackage.Steps.Add(oStep)
    oStep = Nothing
    End Sub

    End Class

    oPumpTask = oTask.CustomTask ==> err unhandled exception:

    "Unable to cast COM object of type 'System.__ComObject' to Interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call the COM component for the Interface with IID
    '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT:0x80004002(E_NOINTERFACE))"


    Please tell me what should I do next?


    thanks alot


    khanhmy

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you using DTS in the first place?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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