Solved

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

Posted on 2013-06-21
9
1,662 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Exceptions 3 40
Entity Framework 7 33
email about the whoisactive result 7 30
Trying to identify overlapping date ranges 5 17
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

16 Experts available now in Live!

Get 1:1 Help Now