October 4, 2012 at 7:30 pm
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...
October 4, 2012 at 11:19 pm
What about controlling the connection with an expretion?
Ian Cockcroft
MCITP BI Specialist
October 5, 2012 at 6:59 am
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, MVP Data Platform Consultant
October 5, 2012 at 8:41 am
Thank you for your reply.So how to do this on expression?
October 6, 2012 at 9:29 am
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.
October 6, 2012 at 8:37 pm
Why do you need the database in single user mode?
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
October 7, 2012 at 11:14 am
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...
October 8, 2012 at 12:38 am
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
October 8, 2012 at 7:15 am
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, MVP Data Platform Consultant
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply