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

  • 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...

  • What about controlling the connection with an expretion?

    Ian Cockcroft
    MCITP BI Specialist

  • 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

  • Thank you for your reply.So how to do this on expression?

  • 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.

  • Why do you need the database in single user mode?

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • 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...

  • 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

  • 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