[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trigger SSIS package on update

Posted on 2011-05-10
7
Medium Priority
?
1,347 Views
Last Modified: 2012-08-13
I have a SSIS package that exports infromation to a flat file and then emails it to a validation service.  I would like to trigger the SSIS package using a trigger on the data table that gets updated fromt he front end interface.  Tha package is built and works great I just want to trigger eveytime a new record is added to a table.  Is this a good idea? Tranactions are a few minutes apart from multiple workstations.  How would I call the SSIS package from a trigger?
0
Comment
Question by:dloebig
  • 4
  • 2
7 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 35732577
0
 

Author Comment

by:dloebig
ID: 35734695
I've tried the Job agent method and am completely confused by the results.  I attached the below code.  When I parse it in the step window everything checks out OK.  When I run it...it says it succeed but it did not.   The SSIS package it is triggering is supposed to create a flat file on the C:drive.  The Job agent is not doing it so I'm not sure why it is thinking it succeeded?  I Can run the SSIS package on its own and it works fine.
DECLARE @Path VARCHAR(200),
    @SQLServer VARCHAR(50),
    @DB VARCHAR(100), 
    @EmailAddress VARCHAR(500),
    @Cmd VARCHAR(4000),
    @ReturnCode INT,
     @Msg VARCHAR(1000)

SELECT @Path = 'C:\SSIS\'
SELECT @SQLServer = 'TEST' 
SELECT @DB = 'VETEST'
SELECT @EmailAddress = 'testmail@sic.com'

SELECT @Cmd = 'DTexec /FILE "' + @Path + 'ecustom.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF  /REPORTING EW'
    + ' /SET \Package.Variables[User::varSourceSQLServer].Properties[Value];' + @SQLServer 
    + ' /SET \Package.Variables[User::varErrorNotifyEmail].Properties[Value];' + @EmailAddress


EXEC @ReturnCode = xp_cmdshell @Cmd

IF @ReturnCode <> 0
BEGIN
   SELECT @Msg = 'SSIS package execution failed for ' + @path + 'ecustom.dtsx on SQL Server\Instance: ' + @SQLServer + '.' + @DB
   EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress , @body = @Msg, @subject = 'SSIS Execution Failure'
END;

Open in new window

0
 

Author Comment

by:dloebig
ID: 35734737
Also, the stored procedure method is not functioning but at least its throwing an error.  This is what its telling me.

Description: The package path referenced an object that cannot be found: "\Package.Variables[User::varSourceSQLServer].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \Package.Variables[User::varSourceSQLServer].Properties[Value] value to TEST.

I'm using this to trigger stored procedure to run:

EXEC dbo.usp_ecustLaunchSSIS 'C:\SSIS\', 'TEST', 'VETEST', 'testmail@sic.com';

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[usp_ecustLaunchSSIS]    Script Date: 05/10/2011 22:07:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: 05/10/2011
-- Description:	
-- Trigger Reference:  EXEC dbo.usp_ecustLaunchSSIS 'C:\SSIS\', 'TEST', 'VETEST', 'testmail@sic.com';
-- =============================================
ALTER PROCEDURE [dbo].[usp_ecustLaunchSSIS]  
    @Path VARCHAR(200),  
    @SQLServer VARCHAR(50), 
    @DB VARCHAR(100), 
    @EmailAddress VARCHAR(500)
AS

SET NOCOUNT ON

DECLARE 
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000)

SELECT @EmailAddress = QUOTENAME(@EmailAddress,'"')
SELECT @SQLServer = QUOTENAME(@@servername,'"')
SELECT @Cmd = 'DTexec /FILE "' + @Path + 'ecustom.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF  /REPORTING EW'
    + ' /SET \Package.Variables[User::varSourceSQLServer].Properties[Value];' + @SQLServer 
    + ' /SET \Package.Variables[User::varErrorNotifyEmail].Properties[Value];' + @EmailAddress

EXEC @ReturnCode = xp_cmdshell @Cmd

IF @ReturnCode <> 0
BEGIN
   SELECT @Msg = 'SSIS package execution failed for ' + @path + 'ecustom.dtsx on SQL Server\Instance: ' + @SQLServer + '.' + @DB
   EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress , @body = @Msg, @subject = 'eCust SSIS Execution Failure'
END

RETURN @ReturnCode

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35735676
Hey, you can do it :

exec xp_cmdshell 'DIR {SSIS package path}'
0
 

Author Comment

by:dloebig
ID: 35737535
using

exec xp_cmdshell 'DIR {C:\SSIS\ecustom.dtsx}'

returns this error

The filename, directory name, or volume label syntax is incorrect.

Tried double quotes as well
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 35737732
Don't have time to Edit your SQL but use the below as a sample.. i have been using this for a while


Change it accordingly from /SQL to /File as you are using file system and also your variables

Also, you will need to enable xp_cmdshell and run using that command.. I have tried using DTExec before, but never helped.so try using xp_cmdshell

CREATE PROCEDURE ProcName
(
@Action Varchar(1), -- 0 OR 1
@DeviceName Varchar(60)
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SQL Varchar(max) 


SET @SQL = '  
EXEC xp_cmdshell ''dtexec /SQL "\RT\Delete ETL" /SERVER "1.1.1.1" /REPORTING N /SET "\Package.Variables[User::Action].Value";"'+@Action+'" /SET "\Package.Variables[User::DeviceName].Value";"'+@DeviceName+'" '''


EXECUTE(@SQL)

END

Open in new window

0
 

Author Comment

by:dloebig
ID: 35739675
This is what I was able to get to work

EXEC xp_cmdshell 'dtexec /FILE "C:\SSIS\ecustom.dtsx" ';

I was hoping to get to a point where variables could be passed in from a config file but for this project it may not be required.  Now I just need to trigger it on insert.

Thanks for the help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 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