cat4larry
asked on
SSIS: Problem adding Row Count into "Send Mail" MessageSource
I put a Row Count Transformation into my DataFlow right between my Flat File Source and my OLE DB Destination. (In debug mode I can see that the correct number of rows are being dumped into the Transformation).
Now I want to email the Row Count using a Send Mail Task. I have the Send Mail Task placed after my Data Flow Task and before the rest of the Control Flow.
Here is where the problems come: I created a local user::variable in the package scope. I use this to hold the value of the Row Count and I set it to a datatype of Int32. When I configure the Send Mail Task (in the Mail tab), I have chosen the MessageSourceType as "variable". But when I attempt to select the MessageSource (using the 3 dot ellipsis next to it) the variable I doesn't appear in the drop down list
I figured maybe the MessageSource doesn't like an Int32 datatype. So I changed the variable to have string datatype. Low and behold it now shows up when I click the ellipsis. However, when I run the package in debug mode I now get this error:
I have done a bunch of searching but no one mentions how to set the local variable so that you can dump the row count into it and ALSO send the variable value out in an email.
Seems like it should be simple, but as usual with MS, the always complicate the simple!
Now I want to email the Row Count using a Send Mail Task. I have the Send Mail Task placed after my Data Flow Task and before the rest of the Control Flow.
Here is where the problems come: I created a local user::variable in the package scope. I use this to hold the value of the Row Count and I set it to a datatype of Int32. When I configure the Send Mail Task (in the Mail tab), I have chosen the MessageSourceType as "variable". But when I attempt to select the MessageSource (using the 3 dot ellipsis next to it) the variable I doesn't appear in the drop down list
I figured maybe the MessageSource doesn't like an Int32 datatype. So I changed the variable to have string datatype. Low and behold it now shows up when I click the ellipsis. However, when I run the package in debug mode I now get this error:
Error at Data Flow Task [Row Count [104]]: The variable "User::FileRowCount" specified by VariableName property is not an integer. Change the variable to be of type VT_I4, VT_UI4, VT_I8, or VT_UI8.
Error at Data Flow Task [SSIS.Pipeline]: "Row Count" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: "Row Count" failed validation and returned validation status "VS_ISBROKEN".
I have done a bunch of searching but no one mentions how to set the local variable so that you can dump the row count into it and ALSO send the variable value out in an email.
Seems like it should be simple, but as usual with MS, the always complicate the simple!
ASKER
yea, that was what I was thinking would need to happen. So I just create the second and use a convert of the first as it's "source"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Rainer
I tried that and it worked great. Thanks!
I tried that and it worked great. Thanks!
ASKER
Gave most of the points to @Rainer as he was "first in". But wanted to give some to @huslayer for the effort.
which version of SSIS?
The variable needs to be int otherwise the data flow component can not store the processed row count.
Then create a second variable of type string and use the expression to convert the int to string. Use this second variable as message source.
HTH
Rainer