Re: DTS connection & BulkInsertTask

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Date: Wed, 5 May 2004 08:48:40 +0100

All my code does is create a text file.

To actually do something with the text file you would need to Add andother
connection, create a datapump task, n transformations objects etc etc.

As I said build the package in designer and then export to VB. This will
show you the things you need to cover in your creating a package. 

-- 
----------------------------
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
"SP" <anonymous@discussions.microsoft.com> wrote in message
news:D95E0285-01E8-42ED-A2DA-515CEB482988@microsoft.com...
> Thanks Allan, It worked for me without any errors.  However, I have a
question:
>
> I am not specifying destination sql server table name, so where will be
the data inserted? Do I need to give the sql server table name in place of
"Text File (Destination)" in  tcn.Name = "Text File (Destination) property?
If not, where to specify the table name?
>
> Thanks alot for your prompt and quick help,
> SP
>
>
>
>
>
>
>      ----- Allan Mitchell wrote: -----
>
>      This is a very quick example
>
>      Dim p As New DTS.Package
>
>      Dim tcn As DTS.Connection
>
>      p.Name = "My Dot Net Package"
>
>      tcn = p.Connections.[New]("DTSFlatFile")
>
>      tcn.DataSource = "C:\MyFile.txt"
>
>      With tcn.ConnectionProperties
>
>      ..Item("Mode").Value = 3
>
>      ..Item("Row Delimiter").Value =
Microsoft.VisualBasic.ControlChars.CrLf
>
>      ..Item("Text Qualifier").Value = """"
>
>      ..Item("File Type").Value = 1
>
>      ..Item("Column Delimiter").Value = ","
>
>      End With
>
>
>
>      tcn.Name = "Text File (Destination)"
>
>      tcn.Reusable = True
>
>      tcn.ConnectImmediate = False
>
>      tcn.ConnectionTimeout = 60
>
>      tcn.UseTrustedConnection = False
>
>      tcn.UseDSL = False
>
>      tcn.ID = 1
>
>      p.Connections.Add(tcn)
>
>
>
>      p.SaveToSQLServer(".", , ,
>      DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , ,
, )
>
>
>
>      You may want to try building a package in designer | saving as a VB
file and
>      looking at what is generated.
>
>
>
>
>
>
>      -- 
>      -- 
>
>      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
>
>
>      "SP" <anonymous@discussions.microsoft.com> wrote in message
>      news:76B3A9E9-7248-4BC2-80C8-AE7FA9C153E1@microsoft.com...
>      > Allan,
>      >> Ya! I am building a package from scratch and not able to create a
text
>      file connection.  The other things you explained clarified some of my
other
>      problems. Thanks for that.  Will you please provide me with an
example, how
>      to create a text file connection in vb.net?  Any references to add
for that?
>      >> Thanks,
>      > SP
>      >>      ----- Allan Mitchell wrote: -----
>      >>      Are you building a package from scratch?
>      >      If so why?
>      >>      No my example does not create a text file connection it will
>      highlight the
>      >      connections that are text files and you can then go on to
manipulate
>      them/it
>      >      .. is a shortcut name for the local server although I
personally do
>      not use
>      >      it I used it here to shield the name of my actual server
>      >      MyPackage is the name of the package I want to load up.  It is
a
>      package
>      >      already built.
>      >>>>      -- 
>      >      -- 
>      >>      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
>      >>>      "sp" <anonymous@discussions.microsoft.com> wrote in message
>      >      news:31475B5B-B7FC-4E71-B702-83EB08E55012@microsoft.com...
>      >> Allan, Can you explain:
>      >>>  p.LoadFromSQLServer(".", ,
>      >
,DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , ,
>      >      ,"MyPackage")
>      >>> 1. Will this make a TextFile Connection to Sql Server 2000?
>      >> 2. Why is "." as first parameter ie Servername? any singnificance
>      for "."?
>      >> 3. "Mypackage" is just a string or do we need to create a package
>      and
>      >      provide that name in the packagename parameter?
>      >>> Thanks a ton,
>      >> sp
>      >>>>>>>      ----- Allan Mitchell wrote: -----
>      >>>      You need to add a ref to the DTS Package Object library
>      >>>      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
>      >>>      '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()
>      >>>>      -- 
>      >>      -- 
>      >>>      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
>      >>>>      "sp" <anonymous@discussions.microsoft.com> wrote in message
>      >>      news:5E8F2BD3-27DF-471D-9BC3-114C07C8FE02@microsoft.com...
>      >>> Hi,
>      >>>> 1. Can anyone tell me how to connect a Text File(source) as
>      >      datasource
>      >>      through programming (not through wizard) in vb.net?
>      >>> 2. Is the Text Qualifier property present in connectionproperties
>      >      of a DTS
>      >>      connection?  If so, is it read only or can we modify the
>      property
>      >      with any
>      >>      text qualifier like single quote, double quotes etc?
>      >>>> Please provide names of necessary com objects to add reference
in
>      >      the
>      >>      project and also syntax of the connection, task &>
connectionproperties
>      >>>> FYI, I am using bulkInsertTask to import from a text file to a
>      sql
>      >      server
>      >>      2000 table.  Is there still a better way to do this job?
>      >>>> Thanks for your help,
>      >>> sp
>      >>>>

Relevant Pages

  • Re: DTS package not picking up changes to UDL file
    ... Data Link Connection ... connection properties from a DTS package into a separate file. ... In Microsoft SQL Server version 7.0, you can use a data link file, but the ...
    (microsoft.public.sqlserver.dts)
  • Re: Catastrophic failure
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > designed the package for the Data Source for my dbf Connection (Source ... > only some time - say the very first time - that to again very rare. ... >> Allan Mitchell MCSE,MCDBA, ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS connection & BulkInsertTask
    ... connection, create a datapump task, n transformations objects etc etc. ... As I said build the package in designer and then export to VB. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> Thanks alot for your prompt and quick help, ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS and MSDE
    ... "Allan Mitchell" wrote: ... box that does the calling of the package as the package runs from the ... I will presume that the package is stored on a SQL Server somewhere. ... I've even used the "sa" login to execute the dts pkg and still ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS object model
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... So one of my collegues suggested to go with DTS and do the validation and also mapping of columns through DTSTransformation. ... I want to create a text file connection and set the> connectionproperties of delimiter and textqualifier and then by using ...
    (microsoft.public.sqlserver.dts)