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
Solved

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

Posted on 2013-06-21
9
1,717 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MailAddress in vb 4 29
VB.net DataGridView show bit field as Text column not CheckBox column 5 18
tempdb log keep growing 7 33
VS 2017 18 32
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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