Index Scripting with Powershell

I’ve been actively seeking out opportunities to do more stuff with Powershell of late.

I had wanted to script out only the Indexes on Tables in a Database and remembered SQL Server Management Studio doesn’t make this as easy as it could be.

I can’t say that I’ve done my due diligence here to find out if SQL Server Data Tools (SSDT) makes this process any easier but I thought I’d have a go at doing it with Powershell just the same.

At the Powershell Command prompt, I add the SQL Server Snap-Ins.

AddSnapIns

I Switch drives to SQLSERVER:\

SetLocationSqlServer

I set my location to the SQL folder, also specifying the SQL Server Instance and Database I’d like to connect to, while navigating the SQL Server Provider Path hierarchy until I hit the Tables Collection.

 

SetLocationTables

I then just type in the following at the prompt to get what I want:

ScriptIndexes

 

The Get-ChildItem command retrieves all the Objects at our current location, in this case, the Tables, and they are piped or passed to the ForEach statement.

The ForEach construct loops over all the available Tables and in turn, their Indexes.

The Script() Method is called on each Index Object to get the TSQL I need.

IndexesScript

 

Doing it with the EzAPI

In my last post, I attempted to write a SQL Server Integration Services (SSIS) Package programmatically.

I used the API that comes with SSIS to build a Package that sourced data from a flat file and loaded it into a Table in a SQL Server Database.

I also mentioned another API that can be used to build SSIS Packages called the EzAPI.

In this post, I intend to build an SSIS Package with the EzAPI. The Package will be functionally identical to the one I built in my last post.

The EzAPI abstracts away some of the complexities of creating SSIS Packages programmatically.  It doesn’t  come as part of the SQL Server Product and as such there is some additional set up work.

You can read a bit more about the API here .

Installation

The EzAPI Project has been published to CodePlex and can be downloaded from there.

The download that supports SSIS 2012 appears to be unavailable so I’ll be using the  version that supports SSIS 2008.

The output would be an SSIS 2008 Package but that’s okay (I hope) because even though I built my last Package with SSIS 2012, I didn’t use any 2012 specific features, Components or Tasks.

Locate the EzAPISetUp.msi file downloaded and run the install program.

The Requirements

Seeing as we’re trying to reproduce our earlier SSIS Package but with the EzAPI, the requirements will obviously be the same.

We would like to automate the creation of SSIS Packages to load flat files into tables in a staging database.

Again, I will be loading the sample flat file below with just a couple of columns and a few rows of fictional customer names.

Customers

 

The Implementation

Start up Visual Studio (I’m using 2012) and create a new C# Console Application Project.  We’ll use the Console Application as a simple Interface to kick off the build and execution of our SSIS Package.

As always, we’ll need to add some Assembly references.

Browse to the folder you installed the EzAPI DLL’s too.  In my case, C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Samples\EzAPI.  Add a reference to the EzAPI.dll.

Browse to the folder SQL Server is installed on your machine.  Open the 100 folder, SDK and then the Assemblies folder.

Search for and add references to the following Assemblies:

Microsoft.SqlServer.DTSRuntimeWrap
Microsoft.SqlServer.ManagedDTS

Replace the code in the Program.cs file in the Console Application Project with the code below.

I have omitted any error handling code for the sake of brevity.

The code above would take the required parameters of the file we want to load and the target SQL Server and database.  It then goes on to create a Table in the database that we would load the file into.

We fetch the Table Column names from the first line of the sample import file.

We’ll put the code that actually creates the SSIS Package in a separate class.

Add a new Class to the Console Application Project by right clicking on the Project name in Solution Explorer, clicking on the Add option and then select Class.

Name the Class EzFileLoaderPackage.cs.

Add the following code to the new EzFileLoaderPackage Class.

The above is all the code we need to create the SSIS Package with the EzAPI.

We start by adding our Connection Managers.

The Flat File Connection Manager we add and configure like this.

We then add a SQL OLEDB Connection Manager to the Package.

We go on to add a DataFlow Task.

Finally, we add a Flat File Source and OLEDB Destination to the DataFlow Task we just created.

All that’s left is to add code that calls our new EzAPI SSIS Package Class.  To do this, return to the Program.cs file and add the following code.

 

The Test

We have done all this within the confines of a Console Application so we just need to supply the required parameters and run it to build and execute our EzAPI SSIS Package.

Copy the sample import file to a location folder of your choosing.

Right click on the Project node in Solution Explorer and click on Properties. Click on the Debug Tab.
Enter the three parameters required in the Command Line Arguments box separated by spaces [file location] [target server] [target database].

RunParameters

Run the Program.

You should see a Console Window come up with a message letting you know when the SSIS Package build and execute process is complete.

FileLoaderRun.jpg

We can now check the output of our program.

You should find a table with the name “Customers”, in the destination database containing the data from our sample file.

CustomersTable

 

CustomerTableData.jpg

 

The Program saves a copy of the SSIS Package it created (.dtsx file) to the folder the sample import file is in.
Create a new SSIS Project in Business Intelligence Development Studio (BIDS), adding the saved .dtsx file or package to the Project.

Again, notice that the package is set up pretty much the same as it would be if you had created it within BIDS.

DataFlowTask

DataFlowComponents

Truncate the Import Table, “Customers”, in the target Database and execute the package from within BIDS and you should have a successful run.

 DataFlowTaskRun

DataFlowComponentsRun

 Check that the destination table is, once again, populated.

CustomerTableData.jpg

 

 

A word on EzAPI Package Templates

One of the interesting features of the EzAPI is its use of Package Templates.

In other words, you can have the API provide you with a better starting point for your Package simplifying the build further.

There just happens to be a template that covers those common occurrences where you are simply moving data from Source to Destination as we are doing in our Package above.

A re-write of our Package with this template is fairly straight forward.

Add a new Class to the Console Application calling it EzFileLoaderPackageWithTemplate.  Add the code below to the Class.

The code is similar to that used to generate our previous Package but with a few notable exceptions.

By inheriting from the Class below we can pre-specify that we would like a Flat File Source, Flat File Connection Manager, OleDbDestination and Sql Ole Db Connection Manager added to the Package for us.

All that’s left to do is to configure the components as necessary and we’re good to go.

To test it, simply change this line of code in the Program.cs file

to this

Run the program as shown above.

The results should be identical to our earlier one.

Creating and Executing an SSIS Package Programmatically

One of the nice things about SQL Server Integration Services (SSIS) is its extensibility.

If you find you have need for a Data Flow Source, Transformation or Destination that doesn’t come stock with SSIS you can roll one of your own. The same goes if you wanted to manage the Control Flow of the Package in some custom way, in which case you can write a Custom Task or use the Script Task.

You can extend SSIS even further by creating or building Packages entirely in code without the aid of the designer that comes with Visual Studio or Business Intelligence Development Studio (BIDS) and this is what I’ll be looking to demonstrate in this post.
I can tell you straight off that it isn’t as intuitive as it could be but that certainly won’t stop us trying  :-)

I’ll be using SQL Server 2012 Integration Services and C# with Visual Studio 2012 to build our example Package.

 

Some simple requirements

So what would we have our Package do? Let’s mock up some requirements.

Let’s say that on a fairly frequent basis you receive tab-delimited flat files with a varying number of columns and content from third parties and you need to load the data in each of these files as is into its own staging Table in a Database for further analysis. Now, of course, you can crack open Visual Studio, create a new SSIS Project and write new Packages to load any one of the files but we would be looking to automate that process here.

I will be using the small flat file below with a couple of columns and a few rows of fictional customer names as an example.

customers_text_file

You can get a copy of the import file here.

 

The Implementation

To give us a “User Interface” from where we can drive the building and execution of our SSIS Package, we’ll use a simple Console Application.

Start up Visual Studio 2012 and create a new C# Console Application Project naming it “FileLoader”.

NewConsoleProject
We will need to add references in Visual Studio to the Assemblies that contain the Classes that we need to build our Package.
You can add a reference to an Assembly in Visual Studio by right clicking on the Project References Folder in Solution Explorer and clicking on “Add Reference”.
Browse to the folder SQL Server 2012 is installed.  Open the 110 folder, SDK and then the Assemblies folder.

Search for and add references to the following Assemblies:

Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SqlServer.DTSRuntimeWrap
Microsoft.SqlServer.ManagedDTS

ReferenceManager
Once the references have been added, the Assembly References Folder of your Console Project, when expanded, should look something like this:

SolutionExplorer

You will need to replace the code in the Program.cs file in the Console Project with the code below.

I have omitted any error handling code for the sake of brevity.

The above is the code needed to build and execute our SSIS Package from end to end.

Our Console Application has been set up to take three parameters.
The location of the file we wish to process and the target Server and Database.

As you would were you using Visual Studio or BIDS to design the Package, we start by creating a new Package.

We will now go on to add the required Connection Managers, Tasks and Data Flow Components to the Package we have just created.

Our requirements specify the need to copy data from a flat file to a Database so we would need to create and configure Flat File and OLEDB Connection Managers.

You can add a Connection Manager to the Package with the following code:

You will need to specify the type of Connection Manager you wish to create as a Parameter to the ADD Method, in our case, it’s “FLATFILE”.

Now we need to further configure the Flat File Connection Manager by specifying it’s Columns, Lengths and Data Types.  We will be importing all data simply as text.

The first line of our import file contains the names we need for our Columns so we read that first line from the file and use it to set our Column names for the Flat File Connection Manager.

 

We’ll add the OLEDB Connection Manager as well.

With both our Connection Managers created, we can now go on to add any Tasks or Data Flow Components we require.
In order to fulfill our requirements we will need a Data Flow Task. A Flat File Source and an OLEDB Destination Component will need to be added to the Data Flow Task.

We add a Data Flow Task to the Packages Executables or Tasks with the following:

Lets add a Flat File Source Component to the Data Flow Task.

Finally, the OLEDB Destination Component.

A Table is also created in the destination Database and is given the same name as our import file,
again our Columns are named using the field names retrieved from the first line of our file earlier. This means that the Column names of the Table we create will match the import file’s field names.

 

So, we have a Data Flow Task that contains a Flat File Source and an OLEDB Destination Component but no Precedence Constraint connecting the two.
We can define a Precedence Constraint between the Flat File Source and the OLEDB Destination Component like this:

All we need to do now is to make the Output Columns from the Flat File Source Component available to the downstream OLEDB Destination Component and map the Columns as necessary.

The code to execute the Package as it stands is a one liner.

We’ll go ahead and save the Package as well so we can have a look at it later.

 

The Test

Now seeing as we’ve set this all up within the confines of a Console Application we just need to supply the required parameters and run it to build and execute our SSIS Package.

Copy the test import file to a location folder of your choosing.

Right click on the Project node in Solution Explorer and click on Properties. Click on the Debug Tab.
Enter the three parameters required in the Command Line Arguments box separated by spaces [file location] [target server] [target database].

RunParameters

Run the Program.

You should see a Console Window come up with a message letting you know when the SSIS Package build and execute process is complete.

FileLoaderRun

We can now check the output of our program.

You should find a table with the name “Customers”, in the destination database containing the data from our sample file.

CustomerTable

 

 

CustomerTableData

 

The Program saves a copy of the Package .dtsx file to the location of the test import file.
Create a new SSIS Project within Visual Studio or BIDS adding the saved .dtsx file or package to the Project.

Notice that the package is set up pretty much the same as it would be if you had created it within the Designer or BIDS.
PackageDataFlowTask

PackageDataFlowComponents

Truncate the Import Table, “Customers”, in the target Database, executing the package from within Visual Studio or BIDS and you should have a successful run.
PackageDataFlowTaskRun

PackageDataFlowComponentsRun

Check that your destination table is, once again, populated.

CustomerTableData

We’ll take a look at the EZApi in my next post, I hear that it’s a potentially more intuitive way to automate the building of SSIS Packages.