Hello,

Does anybody have any code samples/snippets (in VB) to create connections
for the Source (an Acess database) and Destination (SQL 2000) Tables?

I need to move data from Access to SQL. I've written an ActiveXScript for
the data mapping/transformation. Since I intend to run everything from a
stored procedure, I also need to create connections for the Source and
Destination Records programatically.

Any other hints/tips will be most welcome

You can generate your own VB source code. Create a sample package in the
Designer, then select Save As from the package menu, and choose Visual Basic
as the location.

--
Darren Green
http://www.sqldts.com
"sqluser" <sqluser (AT) discussions (DOT) microsoft.com> wrote

Dim oConnection as DTS.Connection2

Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "AdventureWorks2000"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS"

oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "AdventureWorks2000"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

goPackage.Connections.Add oConnection
Set oConnection = Nothing

you must Create Package Object firtst!!

"sqluser" wrote:

When I do the following code in an activeX box in DTS I have an error message
writing that you can't use the property ConnectionProperties

Can you help please?

'on error resume next
err.number=0
Dim goPackageOld
Dim goPackage
Set goPackage = CreateObject("DTS.Package")
goPackage.Name = "Nouveau lot4"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") =
"Alimentation"
oConnection.ConnectionProperties("Data Source") = "ALIM_DEV"
oConnection.ConnectionProperties("Application Name") = "DTS
Designer"
oConnection.Name = "EVOLAN"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "ALIM_DEV"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "Alimentation"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

goPackage.Connections.Add oConnection

Set oConnection = Nothing
goPackage.SaveToStorageFile("c:\temp\monPackage")
set goPackage=nothing


"Kevinlife" wrote: