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 & connectionpropertie

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
s

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_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

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

text qualifier like single quote, double quotes etc?

Allan, Can you explain

p.LoadFromSQLServer(".", , ,DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTru stedConnection, , , ,"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
s



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

You need to add a ref to the DTS Package Object librar

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

'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(


--
--

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


"sp" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:5E8F2BD3-27DF-471D-9BC3-114C07C8FE02 (AT) microsoft (DOT) com..
text qualifier like single quote, double quotes etc

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

,"MyPackage")

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_UseTrus tedConnection, , , , )



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

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 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
S




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

This is a very quick exampl

Dim p As New DTS.Packag

Dim tcn As DTS.Connectio

p.Name = "My Dot Net Package

tcn = p.Connections.[New]("DTSFlatFile"

tcn.DataSource = "C:\MyFile.txt

With tcn.ConnectionPropertie

..Item("Mode").Value =

..Item("Row Delimiter").Value = Microsoft.VisualBasic.ControlChars.CrL

..Item("Text Qualifier").Value = """

..Item("File Type").Value =

..Item("Column Delimiter").Value = ",

End Wit



tcn.Name = "Text File (Destination)

tcn.Reusable = Tru

tcn.ConnectImmediate = Fals

tcn.ConnectionTimeout = 6

tcn.UseTrustedConnection = Fals

tcn.UseDSL = Fals

tcn.ID =

p.Connections.Add(tcn



p.SaveToSQLServer(".", ,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection, , , ,



You may want to try building a package in designer | saving as a VB file an
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 communit
for SQL Server professionals - http://www.sqlpass.or


"SP" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:76B3A9E9-7248-4BC2-80C8-AE7FA9C153E1 (AT) microsoft (DOT) com..
problems. Thanks for that. Will you please provide me with an example, ho
to create a text file connection in vb.net? Any references to add for that

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

"Text File (Destination)" in tcn.Name = "Text File (Destination) property?
If not, where to specify the table name?