Iterate over text file source objects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Iterate over text file source objects

Author

Topic

ciaran
Starting Member

40 Posts

Posted - 2005-08-11 : 07:52:09

Hi,
I have 12 text file (source) objects on my dts package. I need to iterate over these and set their paths dynamically in an active x component. How do i do this.I have stored the paths in an ini file which i initially load into global variables. Here is how i do it for sql connection objects

Function SetupDBConnection()

set pkg = DTSGlobalVariables.parent

dbPortalDataSource= DTSGlobalVariables("PORTALSERVER").value
dbPortalInitialCatalog = DTSGlobalVariables("PORTALDB").value
dbPortalUserId = DTSGlobalVariables("PORTALUSERNAME").value
dbPortalPassword = DTSGlobalVariables("PORTALPASSWORD").value

for each packageConn in pkg.connections
if packageConn.ProviderID = "SQLOLEDB" then
if packageConn.Name = "Portal" then
packageConn.datasource = dbPortalDataSource
packageConn.Catalog = dbPortalInitialCatalog
packageConn.UserId = dbPortalUserId
packageConn.Password = dbPortalPassword
end if
if packageConn.Name = "RBConnection" then
packageConn.datasource = DTSGlobalVariables("CLIENTSERVER").value
packageConn.Catalog = DTSGlobalVariables("CLIENTDB").value
packageConn.UserId = DTSGlobalVariables("CLIENTUSERNAME").value
packageConn.Password = DTSGlobalVariables("CLIENTPASSWORD").value
end if
end if
next
End Function

However my attempt at the text file source is seriously dierespecting me. basically what will i substitute this line with if packageConn.ProviderID = "SQLOLEDB" then

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-15 : 07:58:05

Hi
Replace "SQLOLEDB" with "DTSFlatFile". Just out of interest, why do you need to do this dynamically?

Mark

ciaran
Starting Member

40 Posts

Posted - 2005-08-15 : 12:51:28

i need to keep the dts package independent of where its currently living. ie i need to run the package on different servers. i will read the first part of the path from an ini file

thanks for the reply by he way

quote:
Originally posted by mwjdavidson

Hi
Replace "SQLOLEDB" with "DTSFlatFile". Just out of interest, why do you need to do this dynamically?

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources