calling sql ssis package through asp.net

[janetb99]

I've successfully created an xls import (using the wizard) via SSMS and saved it in sql under msdb on the sql server. I'm able to see it and run it via the sql integration services under stored packages/msdb successfully. The import package locates and retrieves the xls file automatically (no variables - it's always saved and named the same) I'm trying (unsuccessfully) to run the package through asp.net via an sql stored procedure. I've been futzing with it a bit trying different things... nothing working yet, I keep getting at -1. Can anyone help me out? I've read the articles, but I'm a bit lost. The .net is: Dim cmd As New Data.SqlClient.SqlCommand("gme.dbo.sp_housingImport", cn) cmd.Connection.Open() myReturn = (cmd.ExecuteNonQuery) Response.Write("myReturn=" & myReturn.ToString) Stored procedure: DECLARE @ServerName NVARCHAR(100) DECLARE @cmd NVARCHAR(4000) DECLARE @jid UNIQUEIDENTIFIER DECLARE @jname NVARCHAR(128) DECLARE @jobName NVARCHAR(128) DECLARE @FileLocation as varchar(500) -- Create a unique job name SET @ServerName = CONVERT(sysname, SERVERPROPERTY(N'myServerName')) SET @jname = CAST(NEWID() AS CHAR(36)) SET @jobName = @jname SET @FileLocation = 'E:\\myfolder\excel.xls' --set the name and location of the ssis package to run. SET @cmd = '"F:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" ' SET @cmd = @cmd + '/DTS "\MSDB\housingImport" ' SET @cmd = @cmd + '/SERVER ' + @ServerName + ' ' SET @cmd = @cmd + '/CHECKPOINTING OFF ' -- Specify ssis variable value in the package that represents the location of the input file to load SET @cmd = @cmd + '/SET "\Package.Variables[User::varInputFile].Value";"' + @FileLocation + '" ' -- Create job EXEC msdb.dbo.sp_add_job @job_name = @jname, @enabled = 1, --@category_name = 'housingImport', --deletes the job when it is done, regardless of whether or not it was successful @delete_level = 3, @job_id = @jid OUTPUT --Add the job to the Sql Server instance EXEC msdb.dbo.sp_add_jobserver @job_id = @jid, @server_name = '(local)' --Add the step to the job that invokes the ssis package EXEC msdb.dbo.sp_add_jobstep @job_id = @jid, @step_name = 'Execute DTS', @subsystem = 'CMDEXEC', @command = @cmd -- Start job EXEC msdb.dbo.sp_start_job @job_id = @jid

[David Mujica]

Maybe you need to set the command type to StoredProcedure. I believe it would be the 3rd Parameter of Data.SqlClient.SqlCommand Refer to: http://msdn.microsoft.com/en-us/library/system.data.commandtype.aspx[^] Hope this helps. :cool:

[janetb99]

Nope, that doesn't do it.

[Vimalsoft Pty Ltd]

I have a simple way of doing this. i have written an article months ago , where i demonstrate how you can do that in ASP.NET C# http://www.dotnetfunda.com/articles/article786-how-to-execute-ssis-packages-in-csharp-aspnet--part-i-.aspx[^] http://www.dotnetfunda.com/articles/article795-how-to-execute-ssis-packages-in-csharp-aspnet-part-ii-.aspx[^] Hope it helps

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[janetb99]

Vuyiswa Maseko, Thanks for the reply. Tried to follow these two articles: http://www.dotnetfunda.com/articles/article786-how-to-execute-ssis-packages-in-csharp-aspnet--part-i-.aspx\[^\] http://www.dotnetfunda.com/articles/article795-how-to-execute-ssis-packages-in-csharp-aspnet-part-ii-.aspx\[^\] And I'm still having problems. I went back using BMI and created the package. Executes fine. Went into VS2008, and ran the following code:

Try
Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
'Dim sResultDts As DTSExecResult
pkg = app.LoadPackage("F:\Program Files\Microsoft SQL Server\90\DTS\Packages\housingImport2\housingImport2\Package1.dtsx", Nothing)
pkg.Execute()
Catch ex As Exception
Response.Write(ex.ToString)
End Try

I have no rows in my table and no error written to the screen. If I go into the SQL Mgmt Studio under Integrations Services, store packages, file system, housingImport2, housingImport2 and run package1.dtsx, I get 89 rows. Any help?

[Vimalsoft Pty Ltd]

Good Evening janetb99 Does your package Accept any parameters ? If you run your package from SQL as a job it runs fine. I suggest you stepinto the code and debug it and see when it fails and tell me if it go through the exception. One more thing i see you have used a different class for exception try to mimic my code a little bit and use

  catch (DtsException ex)

and you will see there is somewhere in your code where it will give an exception.

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/