Re: DTS object model

From: RK (anonymous_at_discussions.microsoft.com)
Date: 05/05/04 

Date: Wed, 5 May 2004 07:56:13 -0700

Allan, Thanks for your valuable suggestion. I tried with bulkinsert command in TSQL but I also need to validate the data before inserting it into sql tables. So one of my collegues suggested to go with DTS and do the validation and also mapping of columns through DTSTransformation.

Can I do validation & mapping, if I use BulkInsert Command in TSQL?

I appreciate all your prompt replies,
RK
     
     ----- Allan Mitchell wrote: -----
     
     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
>>>>

Relevant Pages

  • Re: Why Oracle does not allow rollback of DDL statements?
    ... TSQL did suffer from a lack of modern exception handling. ... So I think we cannot blame transactional DDL for lacking of exception handlers. ... When porting SQL Server apps to another DBMS the problem of lost errors is a major issue in general since TSQL developers have this nasty tendency to only do error checking wherher they think an error could appear. ...
    (comp.databases.oracle.server)
  • Re: Access Project
    ... Although I have never worked in an Access ADP I believe you need to use TSQL ... You probably want to replace the IIF() constructs with TSQL CASE function. ... Sql Server BOL has a good explanation and lots of examples. ...
    (microsoft.public.access.forms)
  • Re: Importing Multiple text files into one sql table
    ... >the working table you then need to run a series of TSQL ... >ExecuteSQL task. ... >www.allisonmitchell.com - Expert SQL Server Consultancy. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS and DB2/400
    ... If you want the Key then you will have to rewrite to TSQL and apply ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > I am in the process of migrating my database from DB2/400 to SQL Server ... A table with a primary key is being migrated without the primary key ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL Server - Filter
    ... match their selection criteria. ... > and Sql Server and what worked for me. ... > have millions of records, then yes, you can write regular Access queries ... is to write my tSql statements through com ADO. ...
    (comp.databases.ms-access)