Solved

Executing SSIS FROM c#

Posted on 2010-09-23
13
881 Views
Last Modified: 2013-12-16
I have an ssis package that uses a dtscofing file it imports data from a csv file to a sql server table. It works fine when run from Business Intelligence studio. When I call it from C# it fails. I have spend hours debug this code and no progress. Here sample code:

Configuration.ProductConfiguration config = new ProductConfiguration();
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                Package package = app.LoadPackage(ProductConfiguration.UpdateSkuSSIS, null);
                package.ImportConfigurationFile(ProductConfiguration.UpdateSkuConfigFile);
                DTSExecResult result = package.Execute();

Please help!!!
0
Comment
Question by:bitolexus
[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
13 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33745797
See this link.  It will walk you step by step.  

http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 33746273
0
 

Author Comment

by:bitolexus
ID: 33747142
My code is doing something similar. My dtsConfig and dtsx locations are coming from the app.config. I exported all user variables into the dtsconfig. I am assuming they should be automatically loaded . Its really annoying BI studio execute the same package without problem. One I should let you know is that I renamed the package but I don't think that is an issue.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 16

Expert Comment

by:carsRST
ID: 33747261
If you take out the config file and hard code the variables in, does it work?
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33747332
Are you passing in variables from c# or reading from within SSIS?
0
 

Author Comment

by:bitolexus
ID: 33747882
There are 3 variables which hold path for csv loaction, archive folder and error folder. the value are in ssis. my package has a dtsconfig which holds the same values. I am passing dtsx file location and dtsconfig file location from C#. I have tried to invoke validate method of the Package before invoking execute and it returns success.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33747961
Is there error in c# or in SSIS?  Do you have an SSIS error log you can post?
0
 

Author Comment

by:bitolexus
ID: 33749336
no error in ssis, c# returns enum DTSExecResult.Failure
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33750472
Your best bet is to set logging and see exactly what is happening when you run via c#.
http://msdn.microsoft.com/en-us/library/ms141212.aspx

If you can post the log, then Pedro and I may be able to assist what the issue is.
0
 

Author Comment

by:bitolexus
ID: 33752318
Here is my log file
log.txt
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33752735
It looks as if you need to install all of SSIS on the work station running your c# code.  My guess is you do that install and your package should run fine from your app.
0
 
LVL 12

Accepted Solution

by:
ill earned 250 total points
ID: 33849862
Log file suggest, that SQL server editions are not the same. BI studio is probably using SQL developer edition ( basically same as enterprise), but production server runs other( SQL standard perhaps? ).
Functionality that is included in BI studio is missing on server.
Run 'select @@version' to check for status.
0
 

Author Closing Comment

by:bitolexus
ID: 33853605
u r correct
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 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