Solved

Calling an SSIS package from IIS asp.net

Posted on 2011-03-14
4
881 Views
Last Modified: 2012-05-11
I have an intranet.. I am fine most times with the user being prompted to save their file. But I have one daily operation that in the currently run non asp.net project it  saved two files then the users saved a report (all to different locations).. With asp.net this would leave them to run and have to be prompted and to save 3 files to 3 different areas.. I got this working this way, but over the weekend thought about a possibility that I might be able to use a call to an SSIS package to move these files into the correct locations.

I was following the following tutorial.. http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx

Tried both suggestions : Using the package stored on the SQL Server.. Also using the package stored on the C: Drive Both work fine when run in test mode through the localhost.. But when I  put it to IIS, it give me the error seen in the jpg..
protected void btnTest1_Click(object sender, EventArgs e)
        {
            Application app = new Application();
            //
            // Load package from file system
            //
            Package package = app.LoadPackage("C:\\SSISForIIS01\\QuickTest.dtsx", null);
            package.ImportConfigurationFile("c:\\SSISForIIS01\\QuickTest.dtsConfig");

            DTSExecResult result = package.Execute();

     }
       protected void btnTest2_Click(object sender, EventArgs e)
        {

//
            // Load package from SQL Server
            //
            Package package2 = app.LoadFromSqlServer(
                "QuickTest", "SQL01", "usrID", "mypswd", null);

            package2.ImportConfigurationFile("c:\\QuickTest.dtsConfig");

            DTSExecResult result2 = package2.Execute();

            Console.WriteLine("Package Execution results: {0}",
                 result2.ToString());
     }

Open in new window

ProcessSSIS.jpg
0
Comment
Question by:moosetracker
  • 3
4 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 35131197
> C:\\SSISForIIS01\\QuickTest.dtsx

to me it looks like a permission issue, did you try giving QuickTest Directory full permission for your IIS User?
0
 
LVL 1

Author Comment

by:moosetracker
ID: 35131385
I first took the package (which was created on a program server) and moved the QuickTest.dtsx up to my computer C: drive under a folder.. SSISForIIS01..  It worked.

I then created the same folder in the area that holds the IIS, and plopped the same QuickTest.dtsx into that. Into that..  I have a similar folder in the same area for creating temporary reports, XML, Txt files etc, and the IIS programs have no problem reaching this folder as it is a folder on it's own c drive.

The test to reach the SQL server, The C# has the username & password.. This is the same serve that all the projects are using for access to tables and stored procedure. Only of course this isn't  under the Database Engine area, but under the Integrations Services Area..

Those ClassID {...}  Seemed to be similar to what dogged us, when I created a package and my co-worker wanted to use it.. She Definately could not use my project.. Their was a time we got the error even if I uploaded it to the Server and she downloaded it from the server.. But, for some reason that went away and that is now how we mover between each others code..

Therefore I can see this error because I moved it from my users computer to the IIS computer for the attempt to use the package on the C: drive..  I am unsure about why this problem should occur for the one reading off the server..
0
 
LVL 1

Accepted Solution

by:
moosetracker earned 0 total points
ID: 35182784
I figured out an answer myself..  You can set up a job in the SQL Server Job agent..

I do not know if there are other variations but to move the package to the Integrated Sevice area, I used under the "File" toolbar menu  "Save copy of pkgname.dtsx as..."
package location : Sql Server
Server : Name of server
Authentication type :  Sql Server Server Autentication:
Username: _____
password: _____
PackagePath: /QuickTest
ProtectionLevel: Rely on server storage and roles for access controls

Then I created a Job in the SQL Server Agent.. (some of our jobs need the owner to be a proxy, some don't the proxy is needed to move some of the files out to folders outside the server.)
Otherwise in the Job on the SSA under steps..
Type: "SQL Server Integration Services Package"
Package source :  SQL Server
Server : (servename)
Use SQL Server Authentication (username/password)
Browse for your package..

The windows Authentication with hardcoded username/password  is due to IIS does not like integration services..

My SSIS package name is "QuickTest"
My SSA package name is "RunQuickTest"
My stored procedure is "sp_QuickTest"

Now create a Stored procedure to kick off the SQL Server Job Agent..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE sp_QuickTest            
  @SPMessage CHAR (100) OUTPUT,
  @JobName sysname
AS
BEGIN
DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)

set @SPMessage = 'Error'

EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@JobName;

if @ReturnCode = 0
begin
   set @SPMessage = 'Success'
end
else
begin
   set @SPMessage = 'Error'
end;

Return (@ReturnCode);

END
GO


Now Create the C# Code to run the Stored procedure..

            try
            {
                string errMessage = "";
                string ConnectionString = connectionInfo;
                cnnct = new SqlConnection(ConnectionString);
                cnnct.Open();
                cmd = new SqlCommand("sp_QuickTest");
                cmd.CommandType = CommandType.StoredProcedure;


                cmd.CommandTimeout = 0;  // Gives you more time to process.. 0 indicates no limit.

                //if want to read output parm when return, make sure you intialize it in SP!
                //Then set the direction to output.
                //Info Make sure in SP.. you initialize this output field to = '' or it
                // will blow when trying to use.
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@SPMessage", SqlDbType.Char, 100);
                cmd.Parameters["@SPMessage"].Direction = ParameterDirection.Output;
                cmd.Parameters.AddWithValue("@JobName", "RunQuickTest");
                cmd.CommandType = CommandType.StoredProcedure;

                //Process Stored Procedure Query..
                cmd.Connection = cnnct;
                cmd.ExecuteNonQuery();

                errMessage = (String)cmd.Parameters["@SPMessage"].Value;

                switch (errMessage.Trim())
                {
                    case "Success":
                        lblStatus.Text = "SQL Server Agent job, RunQuickTest, started successfully.";
                        break;
                    default:
                        lblError.Text = "SQL Server Agent job, RunSISSPackage, failed to start.";
                        break;
                }

            }
            catch (Exception exp)
            {
                lblError.Text = exp.Message;
            }
            finally
            {
                if (cnnct.State == ConnectionState.Open)
                    cnnct.Close();
            }


0
 
LVL 1

Author Closing Comment

by:moosetracker
ID: 35221205
I came up with the solution on my own..
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vb.net dynamic formulas in runtime 11 74
Syntax error 9 53
Host asp.net pages 5 33
ASP.NET - Bind Gridview Datasource to a specific table in dataset 3 27
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

816 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

6 Experts available now in Live!

Get 1:1 Help Now