Hi

Can anyone tell me how to do: Text File connection to DTS in DTS object model in vb.net?

Thanks
RK

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_UseTrus tedConnection, , , ,
"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 (AT) discussions (DOT) microsoft.com> wrote

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
R

----- Allan Mitchell wrote: ----

You want to create one or manipulate one

Whilst it is possible to build one completely from scratch I would probabl
go with manipulating it

So Say I have a Package called MyPackage and in there I have
TextFileConnection. i can do this to grab a ref to the connectio


Dim p As New DTS.Packag

Dim cn As DTS.Connectio

p.LoadFromSQLServer(".", ,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection, , ,
"MyPackage"

For Each cn In p.Connection

If cn.ProviderID = "DTSFlatFile" Then 'text Fil

'ideally you would know the name of the connectio



End I

Nex

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 communit
for SQL Server professionals - http://www.sqlpass.or


"RK" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:123ED7F8-9268-4575-82EB-2303ADAF3629 (AT) microsoft (DOT) com..

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 (AT) discussions (DOT) microsoft.com> wrote

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.Package
Dim loConn As DTS.Connection
Dim loStep As DTS.Ste
Dim loTask As DTS.Tas
Dim loCustomTask As DTS.BulkInsertTas

Tr
loConn = loPackage.Connections.New("SQLOLEDB"
''loConn = loPackage.Connections.New("DTSFlatFile"

loStep = loPackage.Steps.Ne
loTask = loPackage.Tasks.New("DTSBulkInsertTask"
loCustomTask = loTask.CustomTas

With loCon
.Catalog = scDatabaseName.Tri
.DataSource = scServerName.Tri
.ID =
.UseTrustedConnection = Tru
.UserID = "
.Password = "
End Wit
loPackage.Connections.Add(loConn
loConn = Nothin
With loSte
.Name = "PkgStep
.ExecuteInMainThread = Tru
End Wit
With loCustomTas
.Name = "Task
.DataFile = "c:\customer.txt
.ConnectionID =
.DestinationTableName = scDatabaseName.Trim & "..customertest
.FieldTerminator = ",
.RowTerminator = vbCrLf
.KeepNulls = Tru
End Wit
loStep.TaskName = loCustomTask.Nam
With loPackag
.Steps.Add(loStep
.Tasks.Add(loTask
.FailOnError = Tru
End Wit
loPackage.Execute(

Catch ex As Exceptio
MessageBox.Show("Error: " & CStr(Err.Number) & vbCrLf & Err.Description, vbExclamation, loPackage.Name

Finall
loConn = Nothin
loCustomTask = Nothin
loTask = Nothin
loStep = Nothin
If Not (loPackage Is Nothing) The
loPackage.UnInitialize(
End I
End Tr


Modified to
Dim p As New DTS.Packag
Dim cn As DTS.Connectio

p.Name = "MyPackage
cn = p.Connections.New("DTSFlatFile"
With c
.Catalog = scDatabaseName.Tri
.DataSource = "(local)
.ID =
.UseTrustedConnection = Tru
.UserID = "
.Password = "
End Wit
p.Connections.Add(cn
p.LoadFromSQLServer(".", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection, , , , "MyPackage"

For Each cn In p.Connection
If cn.ProviderID = "DTSFlatFile" Then 'text Fil
'ideally you would know the name of the connectio
'What is the text qualifier propert
MessageBox.Show(cn.ConnectionProperties.Item("Text Qualifier").Value

'Now change i
cn.ConnectionProperties.Item("Text Qualifier").Value = "£

'Have a loo
MessageBox.Show(cn.ConnectionProperties.Item("Text Qualifier").Value
End I
Nex

p.UnInitialize(

*******************
Error occured at p.LoadfromSQLServer(

Where am I going wrong

I appreciate your help
R

----- 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 communit
for SQL Server professionals - http://www.sqlpass.or


"RK" <anonymous (AT) discussions (DOT) microsoft.com> 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 (AT) discussions (DOT) microsoft.com> wrote

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.
"MyPackage")

BULK INSERT has no concept of mapping columns other than through a format
file. Validation you can do through a piece of VBScript in an Active Script
task yes.

a Transformation Object applies to different tasks

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\dts
prog.chm::/dtspcoll_7g6m.htm



--
--

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 (AT) discussions (DOT) microsoft.com> wrote

into sql tables. So one of my collegues suggested to go with DTS and do the
validation and also mapping of columns through DTSTransformation.