Create and execute SSIS packages with Powershell
The other day I was working on a project to migrate the backend of an application to Microsoft SQL Server 2008 R2 from another RDBMS engine. I figured that this would be quite easy to do, but when I tried to migrate the database using the SQL Server Import and Export wizard I started to get errors and the data migration failed. After trying several other methods to migrate the database we finally decided that we would create a version of the database in SQL Server and then use SQL Server Integration Services (SSIS) to migrate the data from the other RDBMS engine to Microsoft SQL Server 2008 R2.
This sounded easy enough until I realized that we would have to create over 900 table mappings between the source and destination databases using the SQL Server Business Intelligence Development Studio (BIDS). I was not looking forward to doing that. So what should I do?
I spent the rest of that afternoon doing some research and I kept coming across blog postings that indicated that I could create an SSIS packages dynamically from a template. This sounded like a good idea and I was intrigued by the concept of automating the creation of all of the SSIS packages that I would need to do the migration of the data from an SSIS template. The only problem with doing that was that I would now have over 900 SSIS packages that I would need to run simultaneously and there was no easy way to do that in BIDS. Bummer!