Solved

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

Posted on 2013-06-21
9
1,689 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
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.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HTML - Color not displaying correctly in EMAIL. 6 37
replicated - directional or bidirectional? 3 29
Query Syntax 17 34
Convert datetime to time string 10 21
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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