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 ...

   ...
}

C# WPF Grid Mouse Event Not Firing

A UIELEMENT in WPF that inherits from Panel, must have a BACKGROUND color in order to bubble a "Click' event.

JavaScript Document Object

Great resource on the JavaScript Document Object.

http://www.comptechdoc.org/independent/web/cgi/javamanual/javadocument.html

C# Directory Security

I wanted to be able to change the permissions of a Folder/File when creating a directory in .NET.

This is pretty simple

            string name = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

            FileSystemAccessRule administratorRule = new FileSystemAccessRule(
                name,
                FileSystemRights.FullControl, 
                InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, 
                PropagationFlags.None, 
                AccessControlType.Allow);

            DirectorySecurity dirSec = new DirectorySecurity();
            dirSec.AddAccessRule(administratorRule);

            Directory.CreateDirectory(@"C:\TestDirCreate\", dirSec);

JavaScipt Template Objects or Object Cloning

After doing a lot of research, I stumbled upon a talk by Nicholas C. Zakas  about Scalable Javascript Architecture.  Great!  Time to learn.


So, Yahoo has developed their own way of doing things.  They have created a 'Module' pattern.  If you watch the talk, he explains it in more detail.  The main idea is that the architecture is, ...well, MODULAR!  The problem is that he explains about the pattern without explaining all the parts that make it possible.


Let's start with creating an object that has 'Public' and 'Private' members and variables.  You start with making an object:

   var MyObj = function () {

                  var _myVar1;
                  var _myVar2;

  }

This will give your object private variables that are visible to any instance of the object, but not publicly visible.

Next, we add the functions that we want to the object:

MyObj.prototype = (function() {

              function func1 () {

              }

              function func2 () {

              }

              return {

                    // DON'T forget the constructor!

                    constructor: MyObj,

                    SomeFunctionName: func2

             }

})();

So, the key is that in the 'return' block, we add the functions we want to make public.  We can give whatever name we want to give, and that is the name with which we can access the function.  For instance,

        var myNewObj = new MyObj();
        myNewObj.SomeFunctionName();  // Will execute func2

We cannot access 'func1'.  It is private.


Next, we can look at trying to decouple objects from one another.  A main point of the talk was to stress the importance of knowledge of the layers.

The architecture looked something like:

                          MODULES
                          SANDBOX
                          APPLICATION CORE
                          LIBRARY


The modules should only know that the 'sandbox' exists, and have no knowledge of any other part of the system.

That's great and all, but how do we decouple the Application Core from the modules?

YUI has a built-in 'Clone' function.  Yay! Except, I don't want to have to incorporate YUI into my project just to use the clone feature.  I happen to use jQuery.

It took me a while to figure out, but I got it with some help from:  http://oranlooney.com/functional-javascript/

So, the reason we want to do this is, we want to be able to create module instances inside the AppCore, without the AppCore knowing about the modules directly.  We 'register' the modules with the AppCore and use a 'Start' method to start the module from the AppCore.

We don't want the AppCore creating direct instances of these modules.  So, the war around that is 'cloning'.

So, we have an object:

        var original = function () {
 
            var _name;
        
        }
 
        original.prototype = (function () {
 
            function Get() {
                return this._name;
            }
 
            function Set(name) {
                this._name = name;
            }
 
            return {
                constructor: original,
                Get: Get,
                Set: Set
            }
        })();

Now we want to use this object to create instances as modules in our system.  We don't want to directly create them, so we must do:

            var obj = new original();
 
            function Clone() { }
            Clone.prototype = obj;
 
            var test1 = new Clone();
            test1.Set("Locke");
            alert(test1.Get());
 
            var test2 = new Clone();
            test2.Set("Shepard");
            alert(test2.Get());
            alert(test1.Get());

This creates two instances of the original, without direct knowledge of it.

NOTE:  In the original object, you MUST use the pre-fix this, otherwise, if the private variable '_name' is changed, it is changed for all instances.  So, any reference to a variable inside that object that you do not want STATIC, you must have 'this.myVar'.  If you want it static throughout all instances, use 'myVar'.

Sites to Check

http://www.google.com/newproducts/
http://www.webdesignerdepot.com/
http://net.tutsplus.com/
http://webdesign.tutsplus.com/
http://www.tutorial9.net/
http://www.shutterstock.com/
http://www.webdesignerwall.com/
http://slashdot.org/
http://www.ted.com/

JavaScipt Array Concat

So, when you concatenate two arrays in javascript, you shouldn't add the original array to the list of arrays to concatenate, unless you want to exponentially grow the array.

{
   var array1 = [ 'John Locke' , 'Jack Shepard' ];
   var array2 = [ 'Michael Scott', 'Dwight Schrute'];

   var array3 = array1.concat(array2);
}

array3 is === [ 'John Locke' , 'Jack Shepard' , 'Michael Scott' , 'Dwight Schrute'];

C# WPF Grid System

There are multiple "root" elements that you can use when designing a WPF application.

I want to talk about the "Grid" element.

This is pretty useful when you have a 'template' layout.  When you know where UI elements are going to be, and/or you are sure that your screen will be resized, you can use the 'Grid' element as your root.

The Grid element is very similar to the Table element in HTML.

A specific question I came across, was "Can I make one row a fixed size, while the other rows grow as the user changes the size of the form?".  This was an issue, because as the user changes the size of the form, the Grid cells, grow and shrink with the form.

Yes, you can set a specific row's height or a column's width.

There are 3 different ways you can set the height of a row.

row.Height = new GridLength(...

The three choices are:

    Pixel - fixed.  The value entered will be the amount of pixels exactly.
    Auto - this will make the row the size needed by the content.
    Star - takes as much as needed, or the amount in percent.

Just thought this was interesting.

CorFlags, C# and InteropServices.COMException (0x80040154): Class not registered

CorFlags is a Visual Studio command line utility that allows an executable that was compiled as (for instance) 'anycpu' to be run on a machine of a different architecture.

Some applications written in VS and compiled with x86, will not run on Windows 7.

The executable needs to be modified using corflags.

The error that caused me to find this issue is

System.Runtime.InteropServices.COMException (0x80040154): Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))

Run this in the VS command line.  Navigate to the executable folder.

corflags [path] /32bit

Now, the /32bit tells the .net framework to run this app as a 32 bit application.  Other options are available.

No applicable name scope exists to resolve the name, Error. C# WPF

This error can occur when:

      A storyboard begins ->  myStoryBoard.Begin(myElement); <- and a programatically created element has not been added to a parent yet.

Add 'myElement' to a parent on the form.

C# WPF Button Blink Problem

In a WPF application, if a button is clicked, the focus gets put on that button.

Sometimes this will cause the button to constantly blink.

In order to stop the button from blinking, some solutions say to put the 'focus' onto another button.

I think, a cleaner way, is to set the 'Focusable' property to false.  This doesn't seem as 'hacky' as setting focus to another button, and still fixes the problem.

HTML 5 Canvas

I wanted to put some HTML 5 Canvas stuff down.

Since HTML 5 is not finalized, some of this stuff might change.

There are a few gotchas that you will need to look out for.

HTML 5 is NOT XHTML.  This means that XHTML standards don't apply.  Attributes do not need quotation marks around values.  Element tags do not need to be closed.  These things are good ideas, but you do not need them.


CANVAS.  Man.  So, if you want to [STYLE] a canvas, there are some things you need to look out for.
To set the "WIDTH" and "HEIGHT" you need to set the "WIDTH" and "HEIGHT" attribute, not the "STYLE".  For instance, instead of style="width:20px; height: 20px;", you set attributes in the tag; like [ width="20" height="20" ].

Javascript Charts

This site is full of javascript chart websites.

http://www.articlediary.com/article/25-graph-chart-solutions-for-web-developers-277.html

Don't know if all are good, but www.highcharts.com is awesome.

C# Stop Form Resize

An easy way to stop a C# WinForm from resizing is to set the 'FormBorderStyle' to any 'Fixed*' property.

(e.g. Fixed3D, FixedSingle, etc).