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

ASP.NET MVC 500.24 Error

This error occurs because Asp.Net cannot apply a detected setting in Integrated managed pipeline mode.

In the Web.config add this line of code in the [system.webserver] block.

[validation validateintegratedmodeconfiguration="false"]

This will ignore the error.


-------------------------


Also, in IIS web.config, in the system.web element, there is the identity impersonate=true element. Delete.

ASP.NET MVC 403 Error

There is an error, when using ASP.NET MVC, that gives you HTTP 403 error.

When, the rest of the MVC site is working, but it cannot access a specific controller, look for a folder named after the controller.

For instance,

If the controller that cannot be accessed is SecurityController, but the rest of the controllers work, look for a folder in the root directory named "Security". ASP.NET MVC tries to access this folder instead of the actual controller.

ASP.NET MVC RC 2 Publish problems

So there was a major problem publishing my ASP.NET MVC site.

This Virtual Directory does not allow contents to be listed.

This was the error. To fix this, you must

1. Open IIS 6

2. Open the properties for your site

3. Goto Home or Virtual Directory

4. Click Configuration (at the bottom)

5. Insert wildcard for aspnet_isapi.dll

You can find better instructions http://haacked.com/archive/2008/11/26/asp.net-mvc-on-iis-6-walkthrough.aspx

Then, in order to have things like MySQL work, you must put any .dll files in the bin. It won't magically transfer during a publish.

Error: 'b' is null or not an object, MicrosoftAjax.js error

There is an error when using MicrosoftAjax.js library for MVC 2.0.

When using the Ajax helper, Ajax.BeginForm, you can specify a OnSuccess javascript function.

This needs to be used as a callback, meaning that you cannot put ().

So...
OnSuccess="MyFunc()" // error needs to be...
OnSuccess="MyFunc"

Finally, answers to how Controllers know about variables on a View

So, it's taken a while, but I've finally figured out how views variables and controllers work, with AJAX.

Ok,

For a controller method to see the value of a tag on a view, you must create a "<[percent]=HTML" tag. You can't just create a regular html tag, you must use the MVC ASP.NET helper.

It Must be in the AJAX form that you are submitting. It cannot be outside the form or the method will not see it.

They method sees the NAME you give the helper tag, NOT the id. So you can have multiple tags with the same name. This is helpful when you want to ajax a lot of functionality but need to keep the same tags but change the values, like knowing which view the user is currently on.

On Complete for AJAX happens when the form is finished submitting, NOT when it finishes loading.

Dec 7, 2009 - It, also, appears that initializing a Html.Hidden(...) field with a @value=".." will not be sent to the server. In other words, if you give @value="..." in the html attributes, the "..." will not be seen on the server. I've found that you must change the value in javascript.

Hope this helps.

Loading Gif with ASP.NET MVC and AJAX

I wanted a loading gif to show while the webapp was doing work. It would take a while. I, also, wanted the gif to go away once the response came back to the user.

Put the loading gif in the div that is replaced. I thought it was a good idea.

User download a file ASP.NET MVC with AJAX

I wanted the user to be able to download a file, using ASP.NET MVC with AJAX. The problem wasn't downloading, or AJAX. It was the two together.

It's really simple. What you need to do is get the path of the file to download, put the value in ViewData and make an href to that file with value "Download" to target="_blank". This will show a popup to download the file.

[lt]a href='[lt]%=path%[gt]' target="_blank" [gt] Download [lt]/a[gt]

[lt] = "<"

[gt] = ">"

Current Directory Path C#

I wanted to get the current directory in a ASP.NET MVC application on the c# side.

System.Environment.CurrentDirectory

-- gets or sets the fully qualified directory.

HOWEVER, this may not be what you are looking for as, this is sometimes

../ProgramFiles/MicroSoftVS/Common7/IDE/

ASP.NET MVC Dropdownlist Values on Server

I wanted to get the values directly from the dropdownlist on the server. For example, I wanted to make a drop down list, and when the user chose either one or multiple values, make a query on the server based on those values.

Well, you can't just write the "select" tag html and expect to get those values on the server. If you want to directly write the html tag, I had to make hidden fields, when the user chose a value in the dropdownlist, make the hidden field's value the value of the dropdownlist.

Well, there is a way. Use the html helper for a dropdownlist.

Before making the dropdownlist, make a dictionary of KeyValuePairs and Values.

The KeyValuePairs are the Key and Values, and the Values are just the values.

You can just make a dictionary and have just the key and values. This will work if you just need the keys on the server side.

The KeyValuePair is just if you want both the key and value on the server. If not, then just make a dictionary of string,string. Where the "new SelectList(d, 'Key', 'Value')" shows, the Key is what is sent to the server, and the value is what is shown to the user. You can change "Key" to "Value" and the value will be sent.

On the server side, you should have a List[string] Name as the variable to receive the data. It can be just "string" but you will only get the first selected key if multple are selected, where the list makes it possible to get all selected keys. If you send back a KeyValuePair as a key, then the values will look like "[key,value]".

Adding a DateTime to a MySQL database

I wanted to add a datetime in an ASP.NET MVC application talking to a MySQL database.  I needed the date in the correct format in order to add it to the database.

This is one way to do it.

The data "cell" that will hold the datetime needs to be a 'DATE' type in MySQL.  Then, the user can set the Month, Day and Year needed.  (e.g. 1/2/2009).

This is the format I used, but you can modify it as according to your application.

So, when adding the date to your database, during your query :

table.datecell = STR_TO_DATE(' " + dateVariable + " ', '%m/%d/%y')

The format %m/%d/%y is month/day/year.  This will set the value of the cell to the date and with a time.

ASP.NET MVC Html.DropDownList

It took me a little while to figure out how to add custom items to an ASP.net MVC Html dropdownlist.  

It takes an IEnumerable of type SelectedList, not just any IEnumerable.  What I had to do was create a List and then make a SelectedList and put that List in the selected list for it to work.

Like this:

ASP.NET MVC usercontrol

There are a few things I found out about ASP.Net MVC user controls.

1. The user control cannot be contained in asp:content blocks.  The code must be in "the open".

2. You can pass the ViewData as a parameter.

3. To render the user control you must:

4. You do not need the '.ascx' after the name of the view.

ASP.NET MVC HTML helpers attributes

In order to set attributes for html elements using

"Html.ActionLink(, , , )"

The attributes should be like

new { @attribute=value, ...})

an example of this would be:

new { @id=doneID, @class="invisible"})

The "doneID" is a string variable that was defined earlier in the ' ' of the page.

The @class value is just a string.

ASP.NET MVC Masterpage Image

I had a problem trying to figure out a way to display an image on a master page and have that image display on all child pages.

I could get it to show the image on all child pages in the IDE, but when I would publish, the image would not show.

My problem was fixed by creating a "class" in my Site.css file.
You can put this code anywhere in that file, but it would be good practice to group classes by logical function.

/*
SAMPLE
*/
.photo-container
{
padding:10px;
border:1px dotted #7a7a7a;
}

To display the image in the master page, this code worked for me.

Make a "div", make the "div" "class='photo-container'". style="width:auto;"> "img src="../../[Folder]/ [File].jpg" />

Type the code where you want your image to be. I hope this helps.