Loop with dinamic connection

Question

Post reply

Loop with dinamic connection

Pivot-156509

SSC Eights!

Points: 943
More actions
June 14, 2005 at 8:23 am

#60847

Hi all!
I'm trying to make a DTS with a loop using a template like this
http://www.sqldts.com/default.aspx?246
I have tried to dynamically change a connection every loop to transfer data from a unique source to a multiple destinations. The first loop run correctly, but the other loop seem that the
connection is again set on the first parameter's loop.
There is an instruction to reset a connection (or close and reopen) when I set the parameter connection?
Can you help me?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

Can you posta copy of the code in your Begin Loop and Loop Around scripts?

Russ

Russel Loski, MCSE Business Intelligence, Data Platform

Pivot-156509 SSC Eights! Points: 943 More actions · Answer 2

IMHO they are not important. The loop run correctly. I have a data transfer in the loop that go from a fixed source to a variable destination (change every loop). I have write an ActiveX that change the connection's parameters. It is right because I have already used it.

What happen:

1. the first loop set correctly the connection that I have parametrized.

2. the loop after (every) I try to change the connection and it is done without errors, but the connection is always on the first DB (that I have set in the first loop)

Sorry for my English!

I hope you can help me.

Russel Loski SSCertifiable Points: 7059 More actions · Answer 3

I really can't say for sure without looking at your code.

You might be looping on one value, but actually setting the connection to the first value every time. You might be setting the wrong property of the connection.

Russel Loski, MCSE Business Intelligence, Data Platform

Russel Loski SSCertifiable Points: 7059 More actions · Answer 4

Try putting at the end of the routine the following line (assuming that you have assigned the connection to conTarget):

msgbox conTarget.DataSource

Does the datasource change as you expect?

Remove this code from production!!!

Russ

Russel Loski, MCSE Business Intelligence, Data Platform

Pivot-156509 SSC Eights! Points: 943 More actions · Answer 5

This is the code of the ActiveX script where I set the connection at every loop.

I can tell you that the table _tmp_connection is managed correctly, I set every loop a different connection. I have debug it.

Function Main()

'------------------------------------------------------------------------------

'Here I get the connection's parameter from the "MSGDB" connection, table _tmp_connection

'After a loop I remove the first line (that I read here)

'------------------------------------------------------------------------------

Dim server

Dim datab

Dim con

Dim rs

Dim query

Dim oConn

Dim oPackage

set oPackage = DTSGlobalVariables.parent

Set oConn=oPackage.connections("MSGDB")

Set con=CreateObject("ADODB.Connection")

Set rs=CreateObject("ADODB.Recordset")

con.Provider="sqloledb"

con.Properties("Initial Catalog")=oConn.Catalog

con.Properties("Data Source")=oConn.Datasource

con.Properties("User Id")=oConn.UserId

con.Properties("Password")=""

con.Open

query="select top 1 * from _tmp_connection order by pk"

rs.Open query, con

if not rs.EOF then

server=rs.Fields("ip")

datab=rs.Fields("dbName")

else

rs.Close

con.Close

Main = DTSTaskExecResult_Failure

exit function

end if

rs.Close

con.Close

'------------------------------------------------------------------------------

' Here I set the connection "DESTINATION_DB" that change at every loop

'------------------------------------------------------------------------------

Dim pkg

if server "" and datab "" then

set oPackage = DTSGlobalVariables.parent

set oConn = oPackage.connections("DESTINATION_DB")

oConn.datasource = server

oConn.Catalog = datab

Main = DTSTaskExecResult_Success

else

Main = DTSTaskExecResult_Failure

end if

End Function

Pivot-156509 SSC Eights! Points: 943 More actions · Answer 6

Yes, is right. I have already debugged the loop and I have verified that I set the right value.

That was be the first test 😉

The problem seem be that after the first setting the connection don't accept change. It connect to the first parameters

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 7

I'm not putting this forward as a definitive solution, but have you thought about using Global Variables to name each of your connections, then when you loop around you increment a counter global variable that will pick up the value of the next connection.

For example:

you have a number of String global variables like Connection1, Connection2 ... and their values match the connection you wish to use for each loop.
you have an integer global variable (x) with a default value of 1
You assign your connections to the datasource using the global variables you've created:

strConnection = DTSGlobalVariables("Connection" & x).value
Set Conn = DTSGlobalVariables.Parent.Connections("myConnection")
Conn.DataSource = strConnections

Remember to increment the value of x every time you perform the loop.

Hope this helps

Phil

Pivot-156509 SSC Eights! Points: 943 More actions · Answer 8

Pivot-156509

SSC Eights!

Points: 943

June 15, 2005 at 4:04 am

#565604

It is the same thing.

Grant Parks Old Hand Points: 364 More actions · Answer 9

I always set both the objConnection.DataSource *and* the objConnection.ConnectionProperties("DataSource").Value

Pivot-156509 SSC Eights! Points: 943 More actions · Answer 10

I post the solution.

It is necessary close all the connection that are dynamic before the next connection would be set.

To do this right click on the "object" (SQL Task or trasformation) that use that connection, click workflow properties, select option tab, check "Close connection on completion".

I thanks the autor of this article http://www.databasejournal.com/features/mssql/article.php/3073161 for the solution.

Bye