Executing SSIS Packages Programmatically

Goal

Our goal is to be able to run packages from another application, outside of BIDS (Business Intelligence Development Studio.  In particular, we want to run these packages from a website.

Problem

Ok, so we know what we want to do.  Let's try it.

We use VS2012 to build our SSIS package, because that's the development tool that our company uses.

We install Integration Services from SQL Server, so that we can "run" our packages locally.

We built a website in ASP.NET MVC,  using VS2013 and wired up everything.  We put our packages into a directory, and loaded the package names into a list.  We show that list to a user, and allow them to choose one to run.

We research how to run packages from code: 
http://technet.microsoft.com/en-us/library/ms136090.aspx

This shows us how to run a package locally.  We add the appropriate references to "Microsoft.SQLServer.ManagedDTS" 12.0 (because that's the newest).

Great!  Let's run it.

Uh oh!  We get an error. 

"To run a SSIS package outside of SQL Server Data Tools you must install..." - there are many different types of this error.

But Wait!?  I have Integration Services installed.  I am doing exactly what the example shows.  Why is this error here?

Google...Google...hours go by...Google...Google

Everyone says the same thing.  Install Integration Services.

Spoiler -> That's not the issue.



Alternative

So, I changed my approach.  I was going to CREATE package programmatically, and then, I could run them programmatically.

I use VS2013 and create a wrapper library for SSIS basically.  It's nice, with interfaces and dependency injection, but that's not important.  I take a package that I created by code, and import it into VS2012 BIDS.  I wanted to see what it looked like on the GUI.

Uh oh!  We get an error.

The version number "8" is higher than the version number used by the system -> "6".

You may know where this is going, but it took me a while. 

So, I, relatively quickly, realized that the version number of my library was different, so I changed it to 11.  I was using 12 in VS2013 and VS2012 needed 11.

Also, in case you were wondering, the 11 version of the XML in the package has some differences other than the version number.  They have longer property values and such.

So, that fixed the import issue.  That should have rang my alarm bell, but it didn't.  I continued for a while, later realizing that I was wrapping SSIS.  Hey, I was stuck.  I didn't know where to turn next.  AND, it wasn't my idea to make packages programmatically; it was a task given to me, so...

Then, it hit me.  I wonder if I change the version of the DLL that I'm using, will the package work.

Yes, it will.

TL, DR :  Make sure the version of "Microsoft.SQLServer.ManagedDTS" that is used to run the SSIS packages is the same as the one used to make them.

IE Hover bug on table.

There is a bug in IE that occurs when a table resides inside of a wrapper. http://blog.brianrichards.net/post/6721471926/ie9-hover-bug-workaround

IE AJAX Json Null Data bug.

I have an ASP.NET MVC 3 project that errors on IE. IE and jQuery ajax have an issue when trying to send data that is null. There is no error, so it's very hard to determine that this is the issue. I have to make separate AJAX calls for each type of call to the .NET controller.

WPF DataGrid Combo Box Databinding

I'm using WPF and MVVM.

I have my View and my ViewModel.

My ViewModel has models of its own.

These models are used to communicate and translate data from a database.

I want my models to be POCOs and not to have more information than each should, for instance, I don't want my POCO model to contain a list of another item just so it knows which one it needs to reference. (This will make sense soon)

I have a PlateConfiguration that references PlateLocations and PlateTypes. I do not want my PlateConfiguration to contain a list of all PateLocations and PlateTypes, just the referenced PlateLocation and PlateType.

To accomplist this through databinding, you need to reference an ancestor and the DataContext of that ancestor.

Here is how:

Value="{Binding Path=DataContext.PlateTypes, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type UserControl}}}"

I am referencing the list of PlateTypes on my ViewModel within an element that has a different context.

The context of my element is:

ItemsSource="{Binding Configurations}"

This changes my context within the element. So, if I try and reference PlateTypes through binding, the element cannot.

Also, I want the selected PlateType to be set on the actual Database Model and not on the ViewModel.

I want the Displayed Value to be the Abbreviation Value that is set by the user.

To display the text of the desired value:

DisplayMemberPath="PlateTypeAbbrev"

This references the Configurations DataContext and not the ViewModel.

The entire code is:

Also,

I've changed the way I bind to my data. It seems more intuitive, but a problem arose, when I tried to change the values. You need to add "Mode=TwoWay, UpdateSourceTrigger=PropertyChanged"
to your binding. If you notice, I have changed the type of column in the DataGrid.

Focus & WPF

Sometimes, I would like to take the focus off an element.

I don't necessarily want to put the focus on another element.

In an instance, where the element is within a "StackPanel", you can do:

// XAML
< stackpanel focusmanager.isfocusscope="True" name="MyStackPanel" > ...

// C#

FocusManager.SetFocusedElement(MyStackPanel, null);

This will not focus on any element, and as importantly, remove focus on any element that has focus in the StackPanel.

C# MySQL store BLOB

I needed to store a BLOB into a MySQL Database.

INSERT INTO `mytable` (`mycolumn1`, `mycolumn2`)
VALUES ('myValue1', @objectData);

_conn = new MySqlConnection();
_conn.ConnectionString = "MyConnectionString";
                   

// You must open the connection before Prepare()

_conn.Open();
 
MySqlCommand nonQueryCommand = new MySqlCommand(query, _conn);
 
nonQueryCommand.Prepare();

// Add parameter. myObjectData is my BLOB from C#
nonQueryCommand.Parameters.AddWithValue("@objectData", myObjectData);
 
nonQueryCommand.ExecuteNonQuery();
_conn.Close();

WPF Hosting Winform

When hosting a WinForm in a WPF application, the WinForm will not render correctly.

This is because in the Program.cs file:

        System.Windows.Forms.Application.EnableVisualStyles();

is called.

You must do this manually.

{
   System.Windows.Forms.Application.EnableVisualStyles();

   WindowsFormsHost wfh ...

   ...
}