SSIS Package Error

Question

SSIS Package Error

etvph81

SSC Veteran

Points: 234
More actions
March 4, 2020 at 1:55 am

#3730753

So I'm developing a SSIS Package to send an attachment with a CSV file and I keep narrowing it down to this one last error everything else works fine except for the following error.

[Execute SQL Task] Error: Executing the query "Declare
@FilePath varchar(max),
@..." failed with the following error: "Incorrect syntax near '@body'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Following script is what I have in my Execute SQL Task
Declare
@FilePath varchar(max),
@filenames varchar(max),
@LoginFile varchar(max),
@EmailAddress varchar(max)
set @FilePath = 'Declare
@FilePath varchar(max),
@filenames varchar(max),
@LoginFile varchar(max),
@EmailAddress varchar(max)
set @FilePath = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\'
set @EmailAddress = ?
Select @LoginFile = @FilePath + 'dc_weekly' + '.csv'
Set @filenames = @LoginFile
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress,
@subject = 'DC Transfer Files',
@body = 'Attached please find Transfer Report.',
@body_format = 'HTML',
@file_attachments = @filenames',
set @EmailAddress = ?
Select @LoginFile = @FilePath + 'Houston_UserLogin' + '.csv'
Set @filenames = @LoginFile
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress,
@subject = 'Transfer Report'
@body = 'Attached please find user login reports.',
@body_format = 'HTML',
@file_attachments = @filenames;

If anyone can help revise my script and point out the error I would appreciate it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 1

I'm seeing dupes in the first 10 lines and an open single quote on the 6th line for starters.

My suggestion would be to make things work in SSMS first and then copy it to the task and make final tweaks from there. That way you'll have the advantages of colored text, etc, to help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

etvph81 SSC Veteran Points: 234 More actions · Answer 2

ok So i fixed it to be correct its only complaining about one thing now thats highlighted in bold

Declare

@FilePath varchar(max),

@filenames varchar(max),

@LoginFile varchar(max),

@EmailAddress varchar(max)

set @FilePath = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\'

set @EmailAddress = '?'

Select @LoginFile = @FilePath + 'dc_weekly' + '.csv'

Set @filenames = @LoginFile

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress,

@subject = 'DC Transfer Files',

@body = 'Attached please find Transfer Report.',

@body_format = 'HTML',

@file_attachments = @filenames,'

set @EmailAddress = ?

Select @LoginFile = @FilePath + ' + '.csv,'

Set @filenames = @LoginFile

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress;

@subject = "Transfer Report"

@body = "Attached please find user login reports.",

@body_format = "HTML",

@file_attachments = @filenames;

Msg 137, Level 15, State 2, Line 29

Must declare the scalar variable "@EmailAddress".

etvph81 SSC Veteran Points: 234 More actions · Answer 3

my apologies i forgot about this

Msg 102, Level 15, State 1, Line 24

Incorrect syntax near '+'.

Msg 102, Level 15, State 1, Line 30

Incorrect syntax near '@subject'.

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 4

Heh... "must look eye".

A "?" is illegal syntax as a value in T-SQL. You need to replace that with a variable or a valid constant for the email address. As for the other problem, it's a couple of simple typ-o's... look here... see anything wrong like a semi-colon instead of a comma on the first line and no comma on the second? How about the double quotes instead of single quotes for string constants???

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress;
@subject = "Transfer Report"
@body = "Attached please find user login reports.",
@body_format = "HTML",

Slow down and check your code. If you have to, read every line and make sure that you check every line for valid punctuation. The machine is telling you the approximate position of the problems... you just need to look carefully to fix them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27045 More actions · Answer 5

I'm sorry. You have now spent what, two days, trying to make this work, and in every case it was syntax errors?

If you correct the file name error in the below code, it should run successfully.

DECLARE @FilePath nvarchar(max) = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\'
DECLARE @Filenames nvarchar(max)
DECLARE @EmailAddress varchar(max)

SET @EmailAddress = '?'

SET @Filenames = @FilePath + 'dc_weekly' + '.csv'

EXEC msdb.dbo.sp_send_dbmail
     @Recipients = @EmailAddress,
     @Subject = N'DC Transfer Files',
     @Body = N'Attached please find Transfer Report.',
     @Body_format = 'HTML',
     @File_attachments = @Filenames;

SET @EmailAddress = '?'

--THIS WILL NOT WORK.  WHAT IS THE NAME OF THE FILE?????
Select @Filenames = @FilePath + '.csv';

EXEC msdb.dbo.sp_send_dbmail
    @Recipients = @EmailAddress, --YOU HAD A SEMI-COLON HERE  ;
@Subject = 'Transfer Report',  --YOU HAD DOUBLE QUOTES HERE, AND NO COMMA
@Body = 'Attached please find user login reports.', --YOU HAD DOUBLE QUOTES HERE
@Body_format = 'HTML', --YOU HAD DOUBLE QUOTES HERE
@File_attachments = @Filenames;

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

Jeff Moden wrote:

A "?" is illegal syntax as a value in T-SQL.

Actually in the context of an SSIS "Execute SQL Task", the ? is valid syntax for parameter substitution. Otherwise yes the OP problems are related to mistakes in commas, semicolons, quotes