Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use same connection manager in different control flow tasks? Expand / Collapse
Author
Message
Posted Thursday, October 4, 2012 7:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:09 PM
Points: 11, 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...
Post #1368796
Posted Thursday, October 4, 2012 11:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:15 AM
Points: 856, Visits: 804
What about controlling the connection with an expretion?

Ian Cockcroft
MCITP BI Specialist
Post #1368821
Posted Friday, October 5, 2012 6:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:15 PM
Points: 3,149, Visits: 794
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/
Post #1369006
Posted Friday, October 5, 2012 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:09 PM
Points: 11, Visits: 110
Thank you for your reply.So how to do this on expression?
Post #1369104
Posted Saturday, October 6, 2012 9:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:09 PM
Points: 11, 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.
Post #1369437
Posted Saturday, October 6, 2012 8:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:15 PM
Points: 3,149, Visits: 794
Why do you need the database in single user mode?

Thomas


Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1369492
Posted Sunday, October 7, 2012 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:09 PM
Points: 11, 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...
Post #1369547
Posted Monday, October 8, 2012 12:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:15 AM
Points: 856, Visits: 804
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
Post #1369628
Posted Monday, October 8, 2012 7:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:15 PM
Points: 3,149, Visits: 794
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/
Post #1369794
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse