June 27, 2012 at 5:12 am
Hi All
I need help with SSIS, I've configured mail and all works perfect and in that mail I attach a file to be sent out but the filename changes on every run. How do I go about and the file format is (EYYMMDD0.dat)
The file get cleared everytime the SSIS runs, so it will be only one file to be attached of that format and sent
Hope the above is clear
Thanks
It's better to fail while trying, rather than fail without trying!!!
June 27, 2012 at 5:17 am
You would have to set the fileattachments property of the send mail task to an expression which takes in the dynamic filename.
Is the filename generated already within the SSIS package and stored in a variable? If so you just need to pass that variable into the properties, otherwise you might need to look another way to get the filename.
June 27, 2012 at 5:25 am
No variable, yes the file is generated and stored on a folder
It's better to fail while trying, rather than fail without trying!!!
June 27, 2012 at 5:27 am
How is the filename generated?
June 28, 2012 at 12:31 am
Thx, I have a solution
It's better to fail while trying, rather than fail without trying!!!
June 28, 2012 at 12:42 am
smthembu (6/28/2012)
Thx, I have a solution
would you like to share it, so that people finding this thread through a search engine may profit of it as well?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 28, 2012 at 1:12 am
Not sure were to upload the file on SSC, but find below
--TEST--
SET NOCOUNT ON
if exists (select * from sys.objects where object_id = object_id('DirListingTEST')
and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) Drop Table DirListingTEST
if exists (select * from sys.objects where object_id = object_id('FileInfoTEST')
and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) Drop Table FileInfoTEST
DECLARE @PathTEST VARCHAR(200), @filenameTEST varchar(100),@FinalFilenameTEST varchar(100)
SET @PathTEST = 'C:\DIRECTORY\'
CREATE TABLE DirListingTEST
(
ListingName VARCHAR(300),
Depth INT,
IsFile INT
)
INSERT INTO DirListingTEST (ListingName,Depth,IsFile)
EXEC Master.dbo.xp_DirTree @PathTEST,1,1
SELECT RowNum = IDENTITY(INT,1,1),
FileName = ListingName,
Processed = 'N'
INTO FileInfoTEST
FROM DirListingTEST
WHERE IsFile = 1
ORDER BY FileName
SET @filenameTEST = (SELECT top 1 ltrim(rtrim(FileName)) FROM FileInfoTEST where Filename like 'E12%'
ORDER BY RowNum desc)
SET @FinalFilenameTEST = @PathTEST + @filenameTEST Print @FinalFilenameTEST
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBAdmin',
@recipients = 'email add',
@subject = 'Subject',
@file_attachments = @FinalFilenameTEST ;
It's better to fail while trying, rather than fail without trying!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply