How to change property of Package

Question

Post reply

How to change property of Package

hendrasd

SSC-Addicted

Points: 413
More actions
June 23, 2004 at 12:25 am

#60195

Hi guys,
I created a Package to transfer data from a dBaseIV file to SQL Database table. Is it possible to change the property of the package dynamically, such as changing the name of the dBase file used in the package or the name of the table ? Do you guys have any other options on how to transfer data from dBaseIV file to SQL Table and vice versa ? Thanks ...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rstack SSC Journeyman Points: 99 More actions · Answer 1

Check out the dynamic properties task in DTS.

BOL

The Dynamic Properties task works by retrieving values from sources outside a Data Transformation Services (DTS) package at package run time and assigning those values to selected package properties. Typically, the external values assigned by the task are unknown until package run time.

Available Source Types

The Dynamic Properties task can assign external data or information to a package property from one of the following sources:

An initialization (*.ini) file, such as Win.ini, or any initialization file that you want to create. This selection only supports property values a single line in length.
A data file containing a property value that can be read and assigned. Unlike the initialization file selection, the data file selection supports property values greater than one line in length.
A query. When you assign the results of a query to a DTS package property, the Dynamic Properties task uses only the results of the first column of the first row. For this reason, consider designing your queries so they generate a single result (for example, a COUNT, SUM or SELECT statement for a particular name).
A DTS package global variable. For more information
An environment variable, which can encompass any available user or system variable (for example, COMPUTERNAME, LOGONSERVER, and so on).
A constant, commonly used to assign a default value to a property in the event a previous assignment fails.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 2

Check out this site. It gives you step by step instructions including screen shots. Helped me out the first time.

http://www.databasejournal.com/features/mssql/article.php/3073161

------------------------------
The Users are always right - when I'm not wrong!

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 3

First, check out this site: http://www.sqldts.com

Related to your question, I dynamically set the password of my connection object – I'm sure you can change the data source as well…

'**********************************************************************

' ActiveX Script

'**********************************************************************

Function Main()

Dim objPkg

Dim objConnection

' Get reference to the Connection object

Set objPkg = DTSGlobalVariables.Parent

Set objConnection = objPkg.Connections("Other (ODBC Data Source)")

objConnection.Password = DTSGlobalVariables("myPwd").Value

Set objConnection = Nothing

Set objPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

Lastly, from BOL:

DataSource Property

The DataSource property specifies a data source name appropriate to the OLE DB provider being used.

Applies To

Connection Object

Connection2 Object

Syntax

object.DataSource [= value]

Part

Description

object

Expression that evaluates to an object in the Applies To list

value

Data source name

Data Type

String

Modifiable

Read/write

Prototype (C/C++)

HRESULT GetDataSource(BSTR *pRetVal);

HRESULT SetDataSource(BSTR NewValue);

Remarks

The data source can be a Microsoft® SQL Server™ name, file name, or some other specification meaningful to the provider.