SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use same connection manager in different control flow tasks?


How to use same connection manager in different control flow tasks?

Author
Message
park1432
park1432
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 110
Task needed to complete:
1) Run sql task to set database to single user
2)Transfer sql server tables in a single database from production to test server using Transfer sql server object task
3) Run sql task to set database back to multi user

I want to use same connection manager used in 1 as a source connection in 2 but every time I open transfer sql server object task to provide source connection it is asking to create new connection. How to use same connection used in 1 to 2? If I create new source connection for 2, it will give me an error: database is used by other login...
Ian C0ckcroft
Ian C0ckcroft
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1236 Visits: 863
What about controlling the connection with an expretion?

Ian Cockcroft
MCITP BI Specialist
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 915
The connection manager uses SMO Connection Manager in the Transfer SQL Server Objects Task, which is different than the connection managers for OLEDB, ADO & ADO.Net in the Execute SQL Task.

You probably need to use a different method for this.

Thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
park1432
park1432
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 110
Thank you for your reply.So how to do this on expression?
park1432
park1432
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 110
Thomas LeBlanc: I am kind of new to SSIS, can you please suggest what will be the alternative way to do this? It will be a great help. Thanks.
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 915
Why do you need the database in single user mode?

Thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
park1432
park1432
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 110
The source is a production database which is linked to front end forms from where users will enter data...so just to make sure that during the time of transferring all the data nothing will be missed out...that's the reason why they want to take database to single user mode then run the SSIS package and set it back to multi-user...
Ian C0ckcroft
Ian C0ckcroft
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1236 Visits: 863
Not sure if this will solve your issue.

Create a connection and copy the connect string from properties.
Add a new variable and call it ParameterString of type string. Paste the connection string as its value. Set the EvaluuteAsExpression to TRUE
Go back to the connection and set the expression ConnectionString to @ConnectionString.

My thinking is that every time it runs, a new connection is created.

Let me know how this works out.
ian

Ian Cockcroft
MCITP BI Specialist
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 915
here are a couple of options:

1. Kill any SPIDs with open connection then manually set to Single User mode.

2. Take the database Offline, dettach databawse, copy MDF and LDF files (and any other database files) to the second instannce and attach to new instance. Attach back to current instance. MAKE SURE YOU COPY THE FILES, DO NOT MOVE THE FILES.

Thanks,

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search