SSIS Failing when I run for to send email out with attachments
-
Hello,
I'm working on a ssis package and I keep getting this error when I try to run this can anyone point out why this could be my parameters are all correct. Please see the attached image.
-
There's nothing attached. But if it works when you run it under your account and not when it runs under a system account, there's likely a permissions issue causing the problem, because the service account probably doesn't have rights to a directory or something that your package is trying to use - like the source folder for the attachments.
-
I'm the admin of the server so I should be able to have this send a email with the source file that I'm pointing it too?
Here is the image I have all my variables matching correctly
-
Did you actually read the error? It appears that your file path is incorrect. There are duplicate "\" in the string.
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/ -
I tried to put duplicate \ the package just shuts down and yes I did read it
-
I guess the next question is why? The slash "\" is being treated as an escape character. I think fixing that error is the first step.
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/ -
I fixed that issue now I'm getting this its not complaining about the slash anymore.
[Execute SQL Task] Error: Executing the query "Declare
@FilePath varchar(max),
@..." failed with the following error: "Attachment file C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmaildc_weekly.csv.csv is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
-
Does this look correct:
C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmaildc_weekly.csv.csv
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/ -
No it doesn’t it should be
C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\dc_weekly.csv
-
So I narrowed it down to my Send Script Task and here is the error:
[Execute SQL Task] Error: Executing the query "Declare
@FilePath varchar(max),
@..." failed with the following error: "Attachment file C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\dc_weekly.csvdc_weekly.csv is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But what I dont understand is my script is correct and what this error is telling me doesn't match up to the script below I doubled checked my path. Any help would be appreciated.
Declare
@FilePath varchar(max),
@filenames varchar(max),
@LoginFile varchar(max),
@EmailAddress varchar(max)
set @FilePath = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\dc_weekly.csv'
set @EmailAddress = ?
Select @LoginFile = @FilePath + 'dc_weekly' + '.csv'
Set @filenames = @LoginFile
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress,
@subject = 'DC Transfer',
@body = 'Attached please find DC Transfer Report.',
@body_format = 'HTML',
@file_attachments = @filenames;
-
Please look at your code, and the error message
The error says that this file name is invalid:
C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\dc_weekly.csvdc_weekly.csv
Your code is far too complicated:
set @FilePath = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\dc_weekly.csv'
set @EmailAddress = ?
Select @LoginFile = @FilePath + 'dc_weekly' + '.csv'
Set @filenames = @LoginFileWhy are you declaring three variables? You need one,
You declare the @filepath and set the value.
You then concatenate 'dc_weekly' + '.csv' to the @filepath into the variable @loginfile
You then set another variable, @filenames, to that value.
Set the variable @file_attachments = @filepath
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/ -
Hi
Thanks for you helping me out I apoligize but I'm new to programming this language my next question is why isn't any of my data showing up in my csv attachment in my package.
Here is the code that actually works:
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'
SET @EmailAddress = 'edward.vahovick@gimi.org'
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 = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\dc_weekly.csv'
- etvph81 wrote:
Hi
Thanks for you helping me out I apoligize but I'm new to programming this language my next question is why isn't any of my data showing up in my csv attachment in my package.
Did you actually put any data in the .csv file? Nothing is going to be in there unless you put it there.
Have you read this:
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/ -
Yes I got the data coming straight from a database in my production server so the package in my flat file destination editor not exactly sure why its not attaching the data and I have not had a chance to read that link you provided.
-
Why are you using SQL Server to send an email with an attachment - when the file is created in SSIS? Why not use the built-in SSIS email task?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply