Creating and excuting a DTS Package in VB.NET 1

[digitor]

Can someone please help. I wish to import a text file using DTS. I need some code to run within VB.NET where i can set a variable to the text file name and then execute the package within vb.net to import the data using DTS into sql server 2000.

Any ideas would be helpful??

 

[tb]

If VB6 code can help you on the right track I'll be happy to supply an example . . .



I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[JohnDTampaBay]

Here's a link about DTS variables and VB.Net...

http://www.sqldts.com/default.aspx?265

...and another about executing the package.

http://www.sqldts.com/default.aspx?208

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[digitor]

I have this package script generated in sql server. I really need the VB.NET equivalent. I tried re-writing it for vb.net but could get around how to work the connection properties i.e. "oConnection.ConnectionProperties" as this isn't liked by .net. So really if someone could write the equivalent vb.net example to this code that would be grateful.

'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: c:\Text VB Script Package.bas
'Package Name: New Package
'Package Description:
'Generated Date: 11/12/2003
'Generated Time: 11:11:14 p.m.
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "New Package"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0



'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("DTSFlatFile&quot

oConnection.ConnectionProperties("Data Source&quot = "c:\data.TXT"
oConnection.ConnectionProperties("Mode&quot = 1
oConnection.ConnectionProperties("Row Delimiter&quot = vbCrLf
oConnection.ConnectionProperties("File Format&quot = 1
oConnection.ConnectionProperties("Column Delimiter&quot = "|"
oConnection.ConnectionProperties("File Type&quot = 1
oConnection.ConnectionProperties("Skip Rows&quot = 0
oConnection.ConnectionProperties("Text Qualifier&quot = """"
oConnection.ConnectionProperties("First Row Column Name&quot = False
oConnection.ConnectionProperties("Number of Column&quot = 0
oConnection.ConnectionProperties("Max characters per delimited column&quot = 255

oConnection.Name = "Text File (Source)"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "c:\data.TXT"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = &quot;<put the password here>&quot;

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer &quot;(local)&quot;, &quot;sa&quot;, &quot;&quot;
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub