February 1, 2016 at 12:31 pm
Hello All,
I need to send the row count from a particular SQL Query to group of people via email every Monday morning. The query logic is pretty simple but I am unable to build SSIS packages out of that. Can you help me with the logic of SSIS?
I understand the row count transformation in SSIS but not sure how to send the row count from the SQL Query.
For those curious below is the SQL Query logic.
Count the number of customers from Customer table from the current week and fiscal of the year. Therefore the query is;
SELECT COUNT(*) FROM tbl_CustomerInfo where FiscalYR='2016' and FiscalWK='4'
February 1, 2016 at 1:10 pm
You won't need the Row Count Transformation for this. Do the following:
1. Create a Variable of type int to hold your count value in.
2. Add an Execute SQL task to your control flow.
3. Configure said task as follows:
>>Connection - connection manager to your DB
>>Result Set - single row
>>SQL Statement - put your query here.
4. On the 'Result Set' tab, click 'add'. Under Result Name, replace the value it gives you with a zero. Place your variable name in the Variable Name box.
You should now have the count from your query in the variable. Now, you'll need to create a 'Send Mail Task' on your control flow. Use an expression to add whatever verbage you want to the MessageSource property. Use the expression to add in your row count variable.
Pretty simple, but let me know if you have any questions/issues getting it set up.
February 1, 2016 at 1:21 pm
Thanks. Everthing is set up till Step 4. But I am unable to configure row count in Send Mail Task. I am not sure I understood what you say. Moreover I tried to set up "Message Source Type" to User Variable under "Expressions" options in Send Mail task but nothing really happen.
February 1, 2016 at 1:46 pm
OK, I'll step it out for you.
1. Add a Send Mail Task to your Control Flow, connect it after success of the previous task we set up (Execute SQL).
2. Open the new send mail task. Go to the Expressions tab.
3. Click on the elipses (...) button, then, under Property column, click in the open space and reveal the property drop down.
4. Select the 'MessageSource' property and open the expression builder.
5. This is where you can build your expression. So, for starters, expand the folder 'Variables and Parameters' to reveal the variable holding your value. Drag that variable down into your 'Expression:' window. Add (DT_WSTR,10) directly in front of your variable to cast it to string. (example: (DT_WSTR,10)@[User::myCount])
6. Click on Evaluate Expression to see the result.
7. Click OK to exit the Expression Builder.
8. Go the the Mail tab and configure the rest of the mail task.
>>SMTP connection
>>From
>>To
>>Subject
>>MessageSource - leave this alone as your expression evaluates this at runtime.
December 21, 2018 at 11:46 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply