Solved

How can I run an SSIS package from my VB.Net application

Posted on 2013-06-21
9
1,631 Views
Last Modified: 2016-02-11
Hi Experts,
I have created a VB.Net (visual studios 2010) application.  I would like to run a SSIS package from my application.  The SSIS package lives in SQL Server 2012.  How can I do this?  

Thanks in advance,
mrotor
0
Comment
Question by:mainrotor
9 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 250 total points
ID: 39267032
Create a job that kicks off the SSIS package and then a proc that kicks off the job.
0
 

Author Comment

by:mainrotor
ID: 39267065
Yes that's exactly what I am trying to do tim_cs, but how do i do it?
0
 

Author Comment

by:mainrotor
ID: 39267069
I already have an SSIS package in my SQL Server 2012 server.  I need to know how to run it from my VB.Net (Visual Studio 2010 app).
0
 

Author Comment

by:mainrotor
ID: 39267164
Hi Experts,
I read a website that said I had to reference the Microsoft.SqlServer.ManagedDTS.dll, but I can't find that .dll on my machine.  Where can I find this .dll?  I have a Windows XP machine.
Thanks in advance,
mrotor.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39267551
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts;




                Microsoft.SqlServer.Dts.Runtime.Application app;
                app = new Microsoft.SqlServer.Dts.Runtime.Application();
                Package pkgIn = new Package();
                DTSExecResult pkgResults_Sql;

                //Get configuration from app.config file as per the batch.

                    pkgIn = app.LoadPackage(ConfigurationSettings.AppSettings["Batch2Package"].ToString(), null);

                    pkgIn.Variables["ConfigFile"].Value = ConfigurationSettings.AppSettings["Batch2Config"].ToString(); \FileSources_Batch02.xml";//"1,2,3,4,5,6,7";

                    pkgIn.Variables["DBDate"].Value = date;

                }

                try
                {
                    // Listen a event from SSIS package.
                    SSISEventListener Listerner = new SSISEventListener();

                    pkgResults_Sql = pkgIn.Execute(null, null, Listerner, null, null);
                    Status = pkgResults_Sql.ToString();
                    int EventCount = Listerner.EventCount;
                    pkgIn.Dispose();

                }
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 250 total points
ID: 39268676
That DLL is part of SQL server SDK which you would need to deploy as part of your program if you want to distribute it. What tim_cs suggest above is an easier approach

1) Create a job (but not enable the schedule)
http://blog.sqlauthority.com/2011/05/23/sql-server-running-ssis-package-in-scheduled-job/

2) Create stored procedure to run the job
http://sqlusa.com/bestpractices2005/startjobfromstoredproc/

3) Execute stored procedure from VB.NET
http://www.dreamincode.net/forums/topic/78574-calling-stored-procedure-in-vbnet/
0
 

Author Comment

by:mainrotor
ID: 39272268
PatelAlpesh,
The problem I have is that I cannot import (i.e. using) in my application.  It cannot find the following:  Import Microsoft.SqlServer.Dts.Runtime
Is there a reference that i am missing?  If so, what do I have to reference?

Thanks in advance,
mrotor
0
 

Author Comment

by:mainrotor
ID: 39279628
CodeCruiser and tim_cs

I create a job that kicks off the SSIS package, and also wrote a stored proc that executes the off the job, as suggested by both of you.   When I run the job in SSMS , I receive a message stating that the job was successful (see image 1 below), but no records get imported.  When  I run the job, using the Execute Package Utility, the job runs fine and all of my records get imported (see image 2 below).  I want to be able run the job from SSMS so that I can eventually put it a Stored Proc that my VB.Net application will use.  What can be causing the job not to run in SSMS, but it runs without any problems using the Execute Package Utility?  How can I fix this problem?

Image 1:

Image1
Image 2:

Image 2
Thanks in advance,
mrotor
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39280087
What can be causing the job not to run in SSMS, but it runs without any problems using the Execute Package Utility?  How can I fix this problem?
It is a totally different context.  When you run as yourself you are running with your account permissions and your workstation.  When you run it as a SQL Server Agent job you are running it using the account for the SQL Server Agent service and you are running it on the server.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now