How to Execute SSIS Packages in C# ASP.NET - Part I

Vuyiswamb
Posted by in Sql Server category on for Intermediate level | Views : 53879 red flag

In this s Article I am going to explain the technology that came with Sql 2005. This is SSIS which stands for Sql Server Integrated Services. Recently I was working on a project for a client with large sum of Data and there was a need to process the data on a certain time frame. There were lot of technologies available at my Disposal, but this kind of Functionality needed to be streamlined and optimised. Of all the Technologies, i have chosen SSIS. In this Article I will demonstrate on how to executing the SSIS Packages in an ASP.NET(C#).
Introduction

Sometime when we develop applications we come across a need where we need to process data that might even time to Process. Most of the time we force technology to do what it was not intend for. In most of the applications that are developed today, there is a need of importing data from different formats. You will hear people talking about export files and import files. The enterprise systems need to be able to do this in an efficient way and an optimised manner.

Background

In this s Article I am going to explain the technology that came with Sql 2005. This is SSIS which stands for Sql Server Integrated Services. Recently I was working on a project for a client with large sum of Data and there was a need to process the data on a certain time frame. There were lot of technologies available at my Disposal, but this kind of Functionality needed to be streamlined and optimised. Of all the Technologies, i have chosen SSIS. In this Article I will demonstrate on how to executing the SSIS Packages in an ASP.NET(C#).

Using the code

We are going to user C# as our language.

Creating a Package

The First thing that we need to do is to create a SSIS package. First to use SSIS you need to install SSIS that comes with your SQL installation cd. If you do not have it, you don’t need to uninstall the current Sql instance, just get the Sql cd and add additional components and choose the SSIS and Business Intelligence. Business Intelligence will be where you will design you packages.   After you have installed the necessary software you can even open any Visual Studio you on your machine and create a new Project as depicted in the following example

After you selected the Project a dialog box will appear as depicted below

Select the Integrated Service Project and Give it a proper name. For the example sake, I will leave it with this default name and click ok and you will be presented with the Following working area as depicted in the Following diagram.

Don’t be afraid this is a piece of cake. Now let us Create an Interesting hello world Package. On your Left hand side there is a toolbar like other types of Project. Click it and let it appear and you will see a different type of Controls there, in fact you have never seen something like this. Later you will explore them on your own but now let’s take a look at them and choose one of them.

Ok Drag and drop the “Execute Sql Task” to the working area and you will have this

Now the red icon that you see there, means that there is something wrong, let is check what is wrong. Move your mouse over the control you just added and you will have a tooltip that will tell you what is wrong as depicted below

Now it says we don’t have a Connection manager. Remember before you execute any Sql statement or Procedures we need to have a connection. At the bottom you will notice that there is connection manager section. Go to it and right click you will be presented with the following options of providers.

Please select “New OLE DB Connection...”  and you will be Presented with the Following dialog box

 

Click on new and you will be presented with another dialog box as depicted below

Now in the Server name choose your Server name and in the “Connect to a Database name” section, choose the database and test the Connection and you will receive a success and Click ok and again to close all the dialog boxes. In the Connection Manager section you will see your connection manager added as depicted

Now we have our Connection manager but the red icon is still there. We need to link it first. Now double click Execute SQL Task control to show the properties as depicted below.

Change the Connection to point the Connection Manager and click on SQL Statement and click the (...) ellipse to have a small window where you will write your SQL statement. Write the SQL statement to add your name to the temp table. I used a solid table so that we can see the results later. Do as depicted in the following diagram and click ok.

And click ok to close the dialog boxes. Now the red icon is gone. No Errors let’s make this interesting. And see what SSIS can do. Right click on the package and select copy as depicted in the following diagram

And paste 3 Times

Now edit their Sql statement by double clicking the controls each and write Different names and last and age to the inserts but now you must not use the select into change them to do insert as depicted below

Change other Controls to do the inserts and when you are done click the First Package and you will notice a green arrow as depicted below

Drag the green arrow and drop it in the second control and the same with the second control and drop it to the third control until the last. And you should line them as I did and when you done you should see this

Now the package is ready to be tested. Press f5. If you see a Green Control it means it’s successfully executed the first step and if it’s red it means there is a problem as depicted below.

Now to see what was the problem move to the Process tab as Depicted in the Following diagram

As you see the age is a string and I treated it as a number and I missed a quote. After I corrected the problem and delete the table that is created and I ran it again and this is the Results.

Now this means our package is ready to be used.

Now right click on your Solution Explorer -> Properties -> Deployment Utilities

Change the Option “CreateDeploymentUtility” to true and click ok. Build the Project and go to the location of the Project on the File System. In the Project Folder Navigate to the bin directory -> Deployment and there will two files as depicted below

The 1kb size file is used to install the package and the other one is the package that we created. Double Click on the 1kb file and install the pack. 

Click next -> Next -> Don’t change anything and Click Finish as depicted in the Following Diagram

Now your Package is installed and it is ready to be executed by a SQL Job or C# code. The Package after its installed is located in the Following files system hierarchy.

Conclusion

This was part one of the series of "How to Execute SSIS Packages in C# ASP.NET" get yourself familiar with SSIS. The second part of this article is is available at http://www.dotnetfunda.com/articles/article795-how-to-execute-ssis-packages-in-csharp-aspnet-part-ii-.aspx .

Thank you for visiting DotnetFunda

Vuyiswa Maseko

Page copy protected against web site content infringement by Copyscape

About the Author

Vuyiswamb
Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.

Login to vote for this post.

Comments or Responses

Posted by: Nishithraj on: 2/18/2010
Actually, I'm curiously waiting for the second part. Because I know until this and like to how you are going to integrate it with asp.net application.
Posted by: Grp on: 8/8/2013 | Points: 25
I create this package, but last created 1 Kb file not working properly (this file not install anything after double, click asking to "Windows cannot open this file" ). Please reply.

Login to post response

Comment using Facebook(Author doesn't get notification)