Getting Started with foreScript

Getting Started with foreScript

I have been using Powershell for some time now and I think that it is a great addition to the toolset of an IT Professional. However the more I used Powershell, the more I found myself having  to write the same pieces of code over and over again in my scripts. Those pieces of code had to do with:

• Getting a list of computers to run the script against - You know the drill and that meant using a cmdlet to get a list of computers from either a csv file, or a text file, or the command line.

•  Getting the information for user authentication - If I wanted to run the script as another user I would end up either prompting for the required credentials, or elevating privileges at run time, or even running Powershell as an administrator.

• Formatting the output of the Powershell scripts for display purposes - This speaks for itself. I have written quite a few Powershell scripts that have code that is only used for converting the  output  into html.

I could have written a module that would handle all of those issues for me and use that module in every Powershell script that I wrote. However I wanted to do much more than that. I wanted to  create a framework that would support the execution of Powershell scripts.

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?