Solved

Where to find list of valid properties which can populate the DTS.Connection-ConnectionProperties collection?

Posted on 2003-11-29
7
568 Views
Last Modified: 2008-02-20
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...

0
Comment
Question by:IMacNeill
  • 3
  • 3
7 Comments
 
LVL 9

Expert Comment

by:miron
ID: 9843133
IMacNeill,

       you need to use SQL Server On - Line documentation.

--cheers
0
 

Author Comment

by:IMacNeill
ID: 9843141
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.

0
 
LVL 9

Expert Comment

by:miron
ID: 9843144
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:IMacNeill
ID: 9843191
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("DTSFlatFile")
With oConn
      .ConnectionProperties("Data Source") = "C:\CleanImport.csv"
      .ConnectionProperties("Mode") = 1
      .ConnectionProperties("Row Delimiter") = "\r\n"
      .ConnectionProperties("Column Delimiter") = ","
      .ConnectionProperties("File Format") = 1
      .ConnectionProperties("File Type") = 1
      .ConnectionProperties("Skip Rows") = 0
      .ConnectionProperties("First 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("SQLOLEDB.1")
With oConn
      .ConnectionProperties("Integrated Security") = "SSPI"
      .ConnectionProperties("Persist Security Info") = True
      .ConnectionProperties("Initial Catalog") = "tblImport"
      .ConnectionProperties("Data Source") = "(local)"
      .ConnectionProperties("Application 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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9843288
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..

DynamicPropertiesTaskSourceType
The DynamicPropertiesTaskSourceType 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
DTSDynamicPropertiesSourceType_Constant 4 Source is a constant.
DTSDynamicPropertiesSourceType_DataFile 5 Source is the contents of a data file.
DTSDynamicPropertiesSourceType_EnvironmentVariable 3 Source is the value of a system environment variable.
DTSDynamicPropertiesSourceType_GlobalVariable 2 Source is the value of a Data Transformation Services (DTS) global variable within the package.
DTSDynamicPropertiesSourceType_IniFile 0 Source is the value of a key within an .ini file.
DTSDynamicPropertiesSourceType_Query 1 Source is a value returned by an SQL query.



See Also

SourceType Property

©1988-2002 Microsoft Corporation. All Rights Reserved.

0
 
LVL 9

Accepted Solution

by:
miron earned 500 total points
ID: 9843361
I will look it up in a minute, menawile could you try to design a little dts pckage simply connecting to the CSV file, go into the disconected edit, and setup the connection properties in the connection object, then go into save package and save it as a visual basic file. That should dump he content of the properties.

What I am going to do is in the object explorer lookup the Data Transformation Services

--cheers
0
 

Author Comment

by:IMacNeill
ID: 9843386
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.

0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Rewrite without the NULLIF 4 25
SQL Pivot table 2 42
SQL Server Sum Over Multiple Tables 20 30
Need SSIS project 2 23
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question