SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Send db mail task fails in SSIS Package with error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maina
Starting Member

1 Posts

Posted - 03/11/2013 :  23:51:39  Show Profile  Reply with Quote
0 down vote favorite


I am trying to execute this sql task in SSIS package which send an email when the file name is not found. I have declared this user-defined variable "@PackageStartTime" in my ssis package. But when my SSIS package hit this task it fails with following error.

"Executing query DECLARE @PackageStartTime Varchar(250) SET @Packag...." failed with the error.: "Parameter name is unrecognized." Possible failure reasons: Problem with the query, "ResultSet" Property not set correctly, parameters not set correctly, or connection not established correctly."

DECLARE @PackageStartTime Varchar(250)
SET @PackageStartTime =?

IF(SELECT COUNT(*)
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime)>0

BEGIN

DECLARE @SUB Varchar(250)
SET @SUB = 'File Failed'+@@SERVERNAME

DECLARE @BODY Varchar(250)
SET @BODY = 'File Failed'+@@SERVERNAME

EXEC msdb.dbo.sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT DISTINCT FileLoadName
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime',
@attach_query_result_as_file=1

I am unable to understand. I have just added a variable User::strPackageStartTime as Datatype = String and Value is blank. I don't have that variable in parameter mapping in Execute SQL Task Editor. Is there I am missing something?

Thank in advance. I am sorry. I am new to this website

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/12/2013 :  00:26:59  Show Profile  Reply with Quote
the easiest way to do this is to create a variable for query (say User::strSQLQuery. Then make evaluate as expression true for it and set expression as

"IF(SELECT COUNT(*)
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime)>0
BEGIN
DECLARE @SUB Varchar(250)
SET @SUB = 'File Failed'+@@SERVERNAME

DECLARE @BODY Varchar(250)
SET @BODY = 'File Failed'+@@SERVERNAME

EXEC msdb.dbo.sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT DISTINCT FileLoadName
FROM [dbo].[Table1] WHERE RowCDate >= " + @[User::strPackageStartTime] + " ',@attach_query_result_as_file=1"

then use this variable inside sql task after making SQLSourceType as variable. You dont need to do any mapping in parameters tab for this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/12/2013 :  00:30:15  Show Profile  Reply with Quote
Hi,

Check this link.. It may help you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=157894
http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000