Hi I have a task - I need to email total number of products sold on the subject line of the mail task. I have Execute SQL task process like select count(*) from tablename. Then I need to Mail the result set like "Total products sold September 2010 : 15000" on teh subject line. I created a variable bit i dont know how to assign it on the subject line of the Send Mail Task. I would really appreciate if you can guide me. Thanks for your help. Febten

Hello, Edit Send mail task, then Click on Expression , then Select Subject and Expression Editor will open there you need to write something like this "Total products sold September 2010 :"+(DT_STR,1252,50)@[User::MyCount] I assume so you have saved the result set of your query (select count(*) from tablename) into variable @[User::MyCount] Save the work and you are done. Note: I hard coded the Month and year in above expression but this can be change according to requirement.You can make this dynamic as well. Edited: Here is link to get the MonthName and you can add that variable in your expression for subject http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ef40789b-a56d-4529-ad0a-ff12f975d7e3 Thanks

Hi Aamir Thanks a lot for your reply. tried your expression , but it throws out the below error. The value, 50, specified for the code page parameter of the cast to data type "DT_STR", is not valid. The code page is not installed on the machine. Attempt to parse the expression ""Total products sold September 2010 :"+(DT_STR,1252,50)@[User::NoofProduct]" failed and returned error code 0xC00470AA. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error. (Microsoft.DataTransformationServices.Controls) Thanks Febten

Hi Aamir When i tried using only the variable on expression i.e @NoofProduct it throws out the below error Cannot convert expression value to property type. Cannot convert 'System.Int32' to 'System.String'. Thanks in advance febten

You need to use the ENTIRE expression that Aamir showed to you without quotes surrounding it all. Talk to me now on

Hello, Sorry, I did a small mistake, Please use this expression -- (DT_STR, 50, 1252).. length of characters should be coming before Code(1252) , I did mistake above , i used code before number of Characters I want to cast to. Please use below Code in expressions. "Total products sold September 2010 :"+ (DT_STR, 50, 1252) @[User::NoofProduct] Thanks

Hi Aamir Thanks a lot it worked fine. Is it possible to Display the Month and Year Dynamically. i.e the September and 2010 must be automatically taken. If i run this package next month - it should Display "Total products sold during October 2010 : 15100" Thanks a lot in advance.

Getting a "September" or "October" value isn't as easy as using a function to retrieve it - there is no MONTHNAME function in SSIS. That said, you can get it to be dynamic by just pulling apart the @[System::StartTime] variable. Something like: "Total products sold " + (MONTH(@[System::StartTime]) == 1 ? "January" : MONTH(@[System::StartTime]) == 2 ? "February" : "and so on...") + " " + (DT_WSTR, 4)YEAR(@[System::StartTime]) + " :"+ (DT_STR, 50, 1252) @[User::NoofProduct] Talk to me now on

Hi Todd Thanks a lot for your reply. Just one more doubt. Is it possible to draw a graph on the Body on the Email. Its like a monthly comparision of the products sold. thanks Febten

You're pushing SSIS beyond the limits of what it's intended to do. Yes, you can do that, but you need some kind of method to display that chart - which means some graphics or charting tool, which means WAY TOO MUCH WORK. What you're really asking for is something that reporting packages do very well - and that's what you should use. Try SQL Server Reporting Services for this part of your SSIS package instead of formulating your "results" or "status" email from within SSIS. You can craft your report to extract and display your data however you like and email it to whomever you want, and just make SSIS launch that report as the last thing it does. Talk to me now on

Hi Todd Thanks for your advise. Really sorry - I am new to this area - just started to learn. I will create a chart using SSRS report. Thanks a lot for your help.

No problem - it takes time to learn where the tools are in the toolbox. I'm just letting you know that you should fasten that screw with a screwdriver, not the hammer you have in your hand. ;) Talk to me now on