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!
