Create and execute SSIS packages with Powershell

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!

A Powershell tool for deleting lines in a csv file based on another csv file

A Powershell tool for deleting lines in a csv file based on another csv file

The other day one of the daily processes that we have to do as a part of our IT Operations failed spectacularly. You see we have to upload a csv file to a transaction processing system so that it could maintain customer information just in case our primary system goes offline.  The problem that we had was that when we tried to upload the file to this system it failed because there were duplicate records already loaded into the database. By the way the systems are dissimilar systems and so some form of replication or log shipping would not have worked in this case. 

This is something that we had seen before and to fix this problem we would run a query against the database to find the duplicate records and then delete the one or two duplicate lines from the file. However this time when we ran the query instead of getting one or two records it returned a few hundred records. This certainly was not good news for us, because there was no way that we could delete a few hundred duplicate lines from the file in time to complete the file upload. Or was there?