Use Container-level variables in a "Execute Sql Task"

Hi PedroCGD

I attempted to run the SqlProfiler - have never used it before and was thus total clueless - if you know of a introductory tutorial, I would liek to learn the tool.

In the interim, I played around and discovered that the variable values are not being fed through to the procedure. I hard-coded assignments (see attached)  in my script and then used the parameters - it worked, with the only issue being the \n (which I used to force a newline.

So, my step, I guess, would be to look at what needs to change with the parameters / parameter mapping.


allanmark

declare @user_name varchar(80)
declare @user_email varchar(80)
declare @support_name varchar(80)
declare @support_email varchar(80)
declare @server_name varchar(80)
declare @server_smtp varchar(80)
declare @message_body varchar(1000)
 
set @user_name = 'allanMark'
set @user_email = 'allanMark@gmail.com'
set @support_name = 'Joe Soap'
set @support_email = 'joesoap@gmail.com'
set @server_name = 'itblue'
set @server_smtp = 'smtp.nana.co.za'
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'
 
 
exec [CE_Extract].dbo.sp_SQLSMTPMail
	@vcTo = @user_email,
	@vcCC= @support_email,
	@vcSubject = 'BranchTarget Update: Failure! ZZZ',
	@vcBody = @message_body,
	@vcFrom = 'BranchTarget.Update@realpeople.co.za',
	@vcSMTPServer = @server_smtp,
	@cSendUsing = '2',
	@vcPort = '25',
	@cAuthenticate = '0',
	@vcSenderName = 'BranchTarget.Update@realpeople.co.za',
	@vcServerName = @server_name

Select allOpen in new window

Its not so complicated to use SQL Profiler in this example. :-)
Follow the images and see the query I exexuted and that SQL profiler catched..
helped?
Regards!
Pedro
SQLProfiler1.JPG
SQLProfiler2.JPG
SQLProfiler3.JPG

What, I uploaded the same picture twice. I never make mistakes. But just in case, here is the second picture.
parameters-mapping.bmp

Thanks.

Did as you suggested. When I try to "Parse Query" it gives me an error - "The query failed to parse.  Syntax error or access violation."

Wondering what I've missed?

declare @user_name varchar(80);
declare @user_email varchar(80);
declare @support_name varchar(80);
declare @support_email varchar(80);
declare @server_name varchar(80);
declare @server_smtp varchar(80);
declare @message_body varchar(1000);
 
set @user_name = ?;
set @user_email = ?;
set @support_name = ?;
set @support_email = ?;
set @server_name = ?;
set @server_smtp = ?;
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'
 
 
exec [CE_Extract].dbo.sp_SQLSMTPMail
        @vcTo = @user_email,
        @vcCC= @support_email,
        @vcSubject = 'BranchTarget Update: Failure! ZZZ',
        @vcBody = @message_body,
        @vcFrom = 'BranchTarget.Update@realpeople.co.za',
        @vcSMTPServer = @server_smtp,
        @cSendUsing = '2',
        @vcPort = '25',
        @cAuthenticate = '0',
        @vcSenderName = 'BranchTarget.Update@realpeople.co.za',
        @vcServerName = @server_name

Select allOpen in new window

Var-Params2.jpg

Hi!

Don't know if this means anything ... I just noticed that my Parameter Mapping doesn't have the "Parameter Size" coulmn as per Hoggzilla's example.

Good pickup re the order -- I have corrected.

I inserted default values -- same error.

OK, my suggestion is to set the BypassPrepare property in the General window to True. This will stop it from checking the SQL Statement for syntax.

Did that.

It pass through as expected but crashe don execution with:

[Execute SQL Task] Error: Executing the query "declare @user_name varchar(80); declare @support_name varchar(80); declare @user_email varchar(80); declare @support_email varchar(80); declare @server_name varchar(80); declare @server_smtp varchar(80); declare @message_body varchar(1000);  set @user_name = ?; set @support_name = ?; set @user_email = ?; set @support_email = ?; set @server_name = ?; set @server_smtp = ?;  set @message_body =  'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +   'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +   'has been placed on the designated network drive and can be opened in Excel. For further ' +   'assistance, please contact ' + @support_name + ' at IT support.' +   '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'   exec [CE_Extract].dbo.sp_SQLSMTPMail       @vcTo = @user_email,       @vcCC= @support_email,       @vcSubject = 'BranchTarget Update: Failure! ZZZ',       @vcBody = @message_body,       @vcFrom = 'BranchTarget.Update@realpeople.co.za',       @vcSMTPServer = @server_smtp,       @cSendUsing = '2',       @vcPort = '25',       @cAuthenticate = '0',       @vcSenderName = 'BranchTarget.Update@realpeople.co.za',       @vcServerName = @server_name " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Me = seriously confused (and loosing mi\ore hair!!).

I got the sql to run and inserted the @message_body into a return variable which I then put to a message box. Attached. Since it is running for me, make sure your EXEC command is syntaxed properly as this is the piece I cannot check.

msgbox-message-body.bmp

Here is my code for what I did. Notice the select statement at the end of the message_body. That is there to return a value and populate a variable.

declare @user_name varchar(80);
declare @user_email varchar(80);
declare @support_name varchar(80);
declare @support_email varchar(80);
declare @server_name varchar(80);
declare @server_smtp varchar(80);
declare @message_body varchar(1000);
 
set @user_name = ?;
set @user_email = ?;
set @support_name = ?;
set @support_email = ?;
set @server_name = ?;
set @server_smtp = ?;
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n';
 
select @message_body;
 

Select allOpen in new window

Now I am confused! It looks liek my problem is the question mark!!

I copied your code and got the same hassle!! When I remove the question marks and use hard-coding, it works!

If you are willing, take your .dtsx file, remove anything sensitive, and paste it here. I will take a look. In the meantime I am posting my example package here for you.
You have to rename the file with a .txt on the end for it to upload. When saving my file, just remove the .txt from the filename. I wish we could upload .dtsx files.
YourPackage.dtsx.txt

PARAMETERS-IN-SQLTASK.dtsx.txt

Many thanks! have attache dmy package.

I added your package to my application.

"Parse Query" gives teh same hassles as I am getting with mine. If I skip prasing and execute the task, it crashes (see atatched snippet).

I am wondering if it isn't perhaps somethign with my configuration?

[Execute SQL Task] Error: Executing the query "declare @user_name varchar(80); declare @user_email varchar(80); declare @support_name varchar(80); declare @support_email varchar(80); declare @server_name varchar(80); declare @server_smtp varchar(80); declare @message_body varchar(1000);   set @user_name = ?; set @user_email = ?; set @support_name = ?; set @support_email = ?; set @server_name = ?; set @server_smtp = ?;   set @message_body =  'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +   'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +   'has been placed on the designated network drive and can be opened in Excel. For further ' +   'assistance, please contact ' + @support_name + ' at IT support.' +   '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n';   select @message_body;    " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 

Select allOpen in new window

Package.dtsx.txt

I will look right away. Remeber, you need to set Bypass Prepare to True. Because of the Input Variables it cannot fully parse and you will get an error. Notice in my package I set the bypass prepare to True.

Hi!

I am stumped!

I created a new project and added your package. With bypassprepare set to false, it fails the Parse. With bypassprepare set to true,  it passes s(obviously, a sit skips it), but the execution fails.

Still looking!

What I noticed was that the Paramater Mapping  -- your's has a "Parameter Size" column and mine does not.

An issue?

Another thought!  Could my issues be service pack related - re my previous post - I've since found out that this column was added with SP2.

GOT IT!!

The answer is Service Pack 2!!

Havign all but given up, I decided to look into why my "Execute Sql Task" parameter Mapping did not have a "Parameter Size". The answer - it was included in Sp2. So, I decided to download SP2 - 282mb on a 384KB ADSL line - took 2hrs to download and then a further hour to install!

I ran your example - it now works!!!

Will tets my Send EMail tonight and post back results.

Awesome, great news!

Thousand apologies. Got side-tracked with a couple of other issues and clean forgot to close this.

Once again - a thousand apologies

PedroCGD - as always, your commensta re an education
HoggZilla - I kept my hair - thankyou