Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-21
9
Medium Priority
?
1,916 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 1000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

718 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