Re: DTS object model

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 05/05/04

  • Next message: Allan Mitchell: "Re: DTS connection & BulkInsertTask" 
    Date: Wed, 5 May 2004 08:46:01 +0100

    If that is all you want to do then why bother with DTS? You can use the
    BULK INSERT command in TSQL and a format file.

    Your code suggests you are building a package from scratch but then you go
    and ask to Load a package from SQL Server with the same name. Maybe you
    wanted SaveToSQLServer ? 

    -- 
----------------------------
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"RK" <anonymous@discussions.microsoft.com> wrote in message
news:59D5FF4E-D38E-4637-8EF0-B7F9F53DB880@microsoft.com...
> Allan,
>
> My requirement is:
> I have a text file with Delimiter as comma and TextQualifier as
SingleQuote.  I want to create a text file connection and set the
connectionproperties of delimiter and textqualifier and then by using
bulkinserttask, I want to insert data into sql server 2000 through DTS
object model in vb.net.
>
> Here is my code snippet:
>
> ********************
> Code prior to modifications:
>         Dim loPackage As New DTS.Package2
>         Dim loConn As DTS.Connection2
>         Dim loStep As DTS.Step
>         Dim loTask As DTS.Task
>         Dim loCustomTask As DTS.BulkInsertTask
>
>         Try
>            loConn = loPackage.Connections.New("SQLOLEDB")
>             ''loConn = loPackage.Connections.New("DTSFlatFile")
>
>             loStep = loPackage.Steps.New
>             loTask = loPackage.Tasks.New("DTSBulkInsertTask")
>             loCustomTask = loTask.CustomTask
>
>             With loConn
>                 .Catalog = scDatabaseName.Trim
>                 .DataSource = scServerName.Trim
>                 .ID = 1
>                 .UseTrustedConnection = True
>                 .UserID = ""
>                 .Password = ""
>             End With
>             loPackage.Connections.Add(loConn)
>             loConn = Nothing
>             With loStep
>                 .Name = "PkgStep"
>                 .ExecuteInMainThread = True
>             End With
>             With loCustomTask
>                 .Name = "Task"
>                 .DataFile = "c:\customer.txt"
>                 .ConnectionID = 1
>                 .DestinationTableName = scDatabaseName.Trim &
"..customertest"
>                 .FieldTerminator = ","
>                 .RowTerminator = vbCrLf
>                 .KeepNulls = True
>             End With
>             loStep.TaskName = loCustomTask.Name
>             With loPackage
>                 .Steps.Add(loStep)
>                 .Tasks.Add(loTask)
>                 .FailOnError = True
>             End With
>             loPackage.Execute()
>
>         Catch ex As Exception
>              MessageBox.Show("Error: " & CStr(Err.Number) & vbCrLf &
Err.Description, vbExclamation, loPackage.Name)
>
>         Finally
>             loConn = Nothing
>             loCustomTask = Nothing
>             loTask = Nothing
>             loStep = Nothing
>             If Not (loPackage Is Nothing) Then
>                 loPackage.UnInitialize()
>             End If
>         End Try
>
>
>
> Modified to:
>            Dim p As New DTS.Package
>             Dim cn As DTS.Connection
>
>             p.Name = "MyPackage"
>             cn = p.Connections.New("DTSFlatFile")
>             With cn
>                 .Catalog = scDatabaseName.Trim
>                 .DataSource = "(local)"
>                 .ID = 1
>                 .UseTrustedConnection = True
>                 .UserID = ""
>                 .Password = ""
>             End With
>             p.Connections.Add(cn)
>             p.LoadFromSQLServer(".", , ,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , ,
"MyPackage")
>
>             For Each cn In p.Connections
>                 If cn.ProviderID = "DTSFlatFile" Then 'text File
>                     'ideally you would know the name of the connection
>                     'What is the text qualifier property
>                     MessageBox.Show(cn.ConnectionProperties.Item("Text
Qualifier").Value)
>
>                     'Now change it
>                     cn.ConnectionProperties.Item("Text Qualifier").Value =
"£"
>
>                     'Have a look
>                     MessageBox.Show(cn.ConnectionProperties.Item("Text
Qualifier").Value)
>                 End If
>             Next
>
>             p.UnInitialize()
>
> ********************
> Error occured at p.LoadfromSQLServer()
>
> Where am I going wrong?
>
> I appreciate your help,
> RK
>
>      ----- Allan Mitchell wrote: -----
>
>      What is your exact string you are using.  Is it exactly the same as
mine?
>
>      -- 
>      -- 
>
>      Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>      www.SQLDTS.com - The site for all your DTS needs.
>      I support PASS - the definitive, global community
>      for SQL Server professionals - http://www.sqlpass.org
>
>
>      "RK" <anonymous@discussions.microsoft.com> wrote in message
>      news:841503A9-09D8-47C1-B2DB-E7FFA3A6CE3E@microsoft.com...
>      > I am getting an error "The specified DTS Package ('Name =
'MyPackage';
>      ID.VersionID =  {[not specified]}.{[not specified]}') does not
exist."
>      >> Do I need to specify any package prior to this?
>      >> Thanks for all your help,
>      > RK
>      >>      ----- Allan Mitchell wrote: -----
>      >>      You want to create one or manipulate one.
>      >>      Whilst it is possible to build one completely from scratch I
would
>      probably
>      >      go with manipulating it.
>      >>      So Say I have a Package called MyPackage and in there I have
a
>      >      TextFileConnection.  i can do this to grab a ref to the
connection
>      >>>      Dim p As New DTS.Package
>      >>      Dim cn As DTS.Connection
>      >>      p.LoadFromSQLServer(".", , ,
>      >
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , ,
>      ,
>      >      "MyPackage")
>      >>      For Each cn In p.Connections
>      >>      If cn.ProviderID = "DTSFlatFile" Then 'text File
>      >>      'ideally you would know the name of the connection
>      >>>>      End If
>      >>      Next
>      >>      p.UnInitialize()
>      >>>      -- 
>      >      -- 
>      >>      Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>      >      www.SQLDTS.com - The site for all your DTS needs.
>      >      I support PASS - the definitive, global community
>      >      for SQL Server professionals - http://www.sqlpass.org
>      >>>      "RK" <anonymous@discussions.microsoft.com> wrote in message
>      >      news:123ED7F8-9268-4575-82EB-2303ADAF3629@microsoft.com...
>      >> Hi,
>      >>> Can anyone tell me how to do: Text File connection to DTS in DTS
>      object
>      >      model in vb.net?
>      >>> Thanks,
>      >> RK
>      >>>
  • Next message: Allan Mitchell: "Re: DTS connection & BulkInsertTask"

    Relevant Pages
    Loading