hen doing repetitive database work with SQL Change Automation (SCA) or SQL Compare, we often need to use other tools at the same time such as the registered servers in SQL Server Management Studio (SSMS), SQLCMD, and BCP. I also tend to use the SQL Server PowerShell module, sqlserver (formerly known as sqlps). This uses Server Management Objects (SMO), which is Nature's Way of interacting with SQL Server and uses the same .NET library that underlies SSMS.
You might also enjoy: Simple Steps in SQL Change Automation Scripting
If you do so, you'll want to integrate all these tools as much as possible, and when you're scripting with PowerShell, use the same database connections as you are using with SCA. This article is all about how you do that. We'll show how you can start integrating SCA scripts with SSMS into a single process, and we'll also learn to stop fearing the connection string and view it as an ally. We will use one to create an SMO connection via a serverConnection object and borrow that same connection to execute BCP and execute a SQL Command.