CODEWITHSUNDEEP
ssisattachment
ETLUser
ETLUser

Reputation: 401

Send mail Task with an attachment in SSIS package

In my ssis Package I have Data Flow task in sequence container. OLEDB is the Source and Flat file is Destination. I have given a variable for the Location of the flat file (@[User::strUnknownFileLocation]). Also for the name of the flat file i have added a variable (@[User::StrUnknownFileName]). After the data flow task I have send mail task. I am attaching that Flat file as an attachment in Send mail Task.

This is the Expression I am evaluating in my Filename Variable (@[User::StrUnknownFileName]). I have set it up the Evaluate Expression is True in property. 

"UnknownValue_"+ (DT_STR,4,1252)YEAR(getdate()) +(MONTH( getdate()) < 10 ? "0" : "")+  (DT_STR,4,1252)MONTH(getdate()) +(DAY( getdate()) < 10 ? "0" : "") + (DT_STR,4,1252)DAY(getdate()) + ".txt"

This is in my flat file connection property expression --Connection string 

@[User::strUnknownFileLocation]+ @[User::StrUnknownFileName]

This is in my flat file connection property expression

Variable for File location I don't have any expression. I have added value of location in Variable

(@[User::strUnknownFileLocation]).

Like M:\ETL\Tmp\ In send mail task I have an attachment and expression for attachment is given like this,

@[User::strUnknownFileLocation]+ @[User::StrUnknownFileName]

I ran the package it was fine yesterday. It was attaching an file as UnknownValue_20130405.txt. But when I open the package today, it had an error in the send mail task. When i click on error, it says it can't find the file UnknownValue_20130406.txt or I don't have permission to open that location. Its kinda weird.

So, I execute the DFT task. So file got generated. Then it works because file is there. So why its not running in the first place. So now tomorrow when I will open my package and try to run, it wont run because there is no file on that date? But if i go and run only tht DFT task it will generate a flat file and it will have a flat file in that location. But this is not how it should be.

Is there something I am doing wrong? I am unable to understand this. Any help will be very appreciated.

Thanks in advance.

Upvotes: 3

Views: 22672

Answers (1)

billinkc
billinkc

Reputation: 61211

I believe you are running into an issue because SSIS, as part of the pre execute phase, validates that all the resources it needs are available. On a new day, the file isn't available until after the package has run.

To get around this, you can tell you SSIS to wait until it actually needs to use a resource before validating it. On any element in SSIS, it should have a DelayValidation property which is set to False by default. Set the value to True on the "Send Mail Task"

Upvotes: 5

Related Questions