IMacNeill
asked on
Where to find list of valid properties which can populate the DTS.Connection-ConnectionProperties collection?
Hello, sorry to bother you with this - and hopefully it is a quick one.
Having trouble trying to track down where Microsoft hides their list of the valid property names which can be set/read for the ConnectionProperties collection of a DTS.Connection object.
I am attempting to setup a VB app which will automatically import a .CSV flat file into a pre-existing SQL2K table. I already have sample code that should get me through, although I am having some trouble with the available properties that can be set on the DTS.Connection object. I would like to access a list of ALL of the possible settings for this object (along with examples, if possible).
Some of the settings I already know are "Row Delimiter", "Column Delimiter", "Data Source" -- although I don't have a clue as to what the possible values are for "File Format", or what "File Type" settings are possible.
As this is urgent (as in I need to complete this project today), I'm assigning this 500 points.
Thanks in advance...
Having trouble trying to track down where Microsoft hides their list of the valid property names which can be set/read for the ConnectionProperties collection of a DTS.Connection object.
I am attempting to setup a VB app which will automatically import a .CSV flat file into a pre-existing SQL2K table. I already have sample code that should get me through, although I am having some trouble with the available properties that can be set on the DTS.Connection object. I would like to access a list of ALL of the possible settings for this object (along with examples, if possible).
Some of the settings I already know are "Row Delimiter", "Column Delimiter", "Data Source" -- although I don't have a clue as to what the possible values are for "File Format", or what "File Type" settings are possible.
As this is urgent (as in I need to complete this project today), I'm assigning this 500 points.
Thanks in advance...
ASKER
If you can tell me what book/section I should be looking in, I'll give you the points...it's an awfully big document after all.
here is some additional links that should be helpful,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_pkgmng_des_8bw4.asp?frame=true
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspcoll_76lq.asp
http://www.sqldts.com
http://www.sqlconsultants.co.uk/vbDTS.htm
--cheers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_pkgmng_des_8bw4.asp?frame=true
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspcoll_76lq.asp
http://www.sqldts.com
http://www.sqlconsultants.co.uk/vbDTS.htm
--cheers
ASKER
Thanks for the links miron...although I think I'll try to clarify this question a little in hopes it will yield what I'm looking for.
Specifically, when I create a DTS connection object:
Dim oPkg As New DTS.Package2
Dim oConn As DTS.Connection
Then initialize two connections and bind them to the package:
' Create connection to text file and assign to package
Set oConn = oPkg.Connections.New("DTSF latFile")
With oConn
.ConnectionProperties("Dat a Source") = "C:\CleanImport.csv"
.ConnectionProperties("Mod e") = 1
.ConnectionProperties("Row Delimiter") = "\r\n"
.ConnectionProperties("Col umn Delimiter") = ","
.ConnectionProperties("Fil e Format") = 1
.ConnectionProperties("Fil e Type") = 1
.ConnectionProperties("Ski p Rows") = 0
.ConnectionProperties("Fir st Row Column Names") = False
End With
oPkg.Connections.Add oConn
Set oConn = Nothing
' Create connection to SQL server and assign to package
Set oConn = oPkg.Connections.New("SQLO LEDB.1")
With oConn
.ConnectionProperties("Int egrated Security") = "SSPI"
.ConnectionProperties("Per sist Security Info") = True
.ConnectionProperties("Ini tial Catalog") = "tblImport"
.ConnectionProperties("Dat a Source") = "(local)"
.ConnectionProperties("App lication Name") = "DTS Import/Export Wizard"
.ID = 2
.Catalog = "tblImport"
.DataSource = "(local)"
.UseTrustedConnection = True
End With
oPkg.Connections.Add oConn
Set oConn = Nothing
...I am setting the properties for two connections; one to a flat text file ("DTSFlatFile"), and another to the SQL server ("SQLOLEDB.1"). Is there a specific section of the SQL Server Online Books, or any other online documentation, that provides a complete reference of possible properties for each connection provider type?
Something that would show that for a flat file, the property of "File Format" has -n- number of possible settings, detailing what each one means...
Hope this helps to clarify...and thanks again.
- IMacNeill
Specifically, when I create a DTS connection object:
Dim oPkg As New DTS.Package2
Dim oConn As DTS.Connection
Then initialize two connections and bind them to the package:
' Create connection to text file and assign to package
Set oConn = oPkg.Connections.New("DTSF
With oConn
.ConnectionProperties("Dat
.ConnectionProperties("Mod
.ConnectionProperties("Row
.ConnectionProperties("Col
.ConnectionProperties("Fil
.ConnectionProperties("Fil
.ConnectionProperties("Ski
.ConnectionProperties("Fir
End With
oPkg.Connections.Add oConn
Set oConn = Nothing
' Create connection to SQL server and assign to package
Set oConn = oPkg.Connections.New("SQLO
With oConn
.ConnectionProperties("Int
.ConnectionProperties("Per
.ConnectionProperties("Ini
.ConnectionProperties("Dat
.ConnectionProperties("App
.ID = 2
.Catalog = "tblImport"
.DataSource = "(local)"
.UseTrustedConnection = True
End With
oPkg.Connections.Add oConn
Set oConn = Nothing
...I am setting the properties for two connections; one to a flat text file ("DTSFlatFile"), and another to the SQL server ("SQLOLEDB.1"). Is there a specific section of the SQL Server Online Books, or any other online documentation, that provides a complete reference of possible properties for each connection provider type?
Something that would show that for a flat file, the property of "File Format" has -n- number of possible settings, detailing what each one means...
Hope this helps to clarify...and thanks again.
- IMacNeill
hi ,
yes MS are usually very obscure with this information...
is this any help ...
if you go into BOL and type DTSConn for an index search that may help....
but as you know ther references just seem to be circular in nature and give frustrating small glimpses of what you are often tryinh to find..
DynamicPropertiesTaskSourc eType
The DynamicPropertiesTaskSourc eType constants are used with the SourceType property to specify the type of source object that provides the value to which a Data Transformation Services (DTS) package object property will be set by the DynamicPropertiesTask object.
Symbol Value Description
DTSDynamicPropertiesSource Type_Const ant 4 Source is a constant.
DTSDynamicPropertiesSource Type_DataF ile 5 Source is the contents of a data file.
DTSDynamicPropertiesSource Type_Envir onmentVari able 3 Source is the value of a system environment variable.
DTSDynamicPropertiesSource Type_Globa lVariable 2 Source is the value of a Data Transformation Services (DTS) global variable within the package.
DTSDynamicPropertiesSource Type_IniFi le 0 Source is the value of a key within an .ini file.
DTSDynamicPropertiesSource Type_Query 1 Source is a value returned by an SQL query.
See Also
SourceType Property
©1988-2002 Microsoft Corporation. All Rights Reserved.
yes MS are usually very obscure with this information...
is this any help ...
if you go into BOL and type DTSConn for an index search that may help....
but as you know ther references just seem to be circular in nature and give frustrating small glimpses of what you are often tryinh to find..
DynamicPropertiesTaskSourc
The DynamicPropertiesTaskSourc
Symbol Value Description
DTSDynamicPropertiesSource
DTSDynamicPropertiesSource
DTSDynamicPropertiesSource
DTSDynamicPropertiesSource
DTSDynamicPropertiesSource
DTSDynamicPropertiesSource
See Also
SourceType Property
©1988-2002 Microsoft Corporation. All Rights Reserved.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually miron, never mind - I think you've allowed me to solve my dilema without needing to know all the settings.
I was not aware that you could save the package as a visual basic file...which negates my need to know the connection properties settings.
Sorry LFS, I gotta dump the points onto miron...
Thanks alot, and sorry for my ignorance.
I was not aware that you could save the package as a visual basic file...which negates my need to know the connection properties settings.
Sorry LFS, I gotta dump the points onto miron...
Thanks alot, and sorry for my ignorance.
you need to use SQL Server On - Line documentation.
--cheers