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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » How to use same connection manager in...

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

Rate Topic

Display Mode

Topic Options

Author

Message

park1432

Posted Thursday, October 04, 2012 7:30 PM

Grasshopper

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

Ian C0ckcroft

Posted Thursday, October 04, 2012 11:19 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 842, Visits: 739

What about controlling the connection with an expretion?

Ian Cockcroft
MCITP BI Specialist

Post #1368821

Thomas LeBlanc

Posted Friday, October 05, 2012 6:59 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 2,700, Visits: 717

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

park1432

Posted Friday, October 05, 2012 8:41 AM

Grasshopper

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

park1432

Posted Saturday, October 06, 2012 9:29 AM

Grasshopper

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

Thomas LeBlanc

Posted Saturday, October 06, 2012 8:37 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 2,700, Visits: 717

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

park1432

Posted Sunday, October 07, 2012 11:14 AM

Grasshopper

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

Ian C0ckcroft

Posted Monday, October 08, 2012 12:38 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 842, Visits: 739

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

Thomas LeBlanc

Posted Monday, October 08, 2012 7:15 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 2,700, Visits: 717

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 »

Permissions