variables - SSIS Script Task Debugging When Checking Value Comparison -

i have code in script task:

public sub main()     '     ' add code here     '     dim monthfromsql string     dim lastmonth new date(datetime.today.year, datetime.today.month - 1, 1)     dim rcnt integer     dim msg string     'msgbox("month name sql " & cstr(dts.variables("monthnamefromsql").value))     rcnt = cint(dts.variables("rowcount").value)     if rcnt = 0             msg = "job returned 0 rows month " & cstr(monthname(lastmonth.month, false)) & " - check database operator previous month's data has been loaded database."      else             msg = "job returned " & rcnt & " rows - job finished"      end if     'pass message variable value out used in message      dts.variables("emailmessage").value = msg     dts.taskresult = dts.results.success end sub 

if modify if statement below able check month value sql db against system month value gives me dts script error saying that:

the type of value being assigned variable "user::emailmessage" differs current variable type. variables may not change type during execution. variable types strict, except variables of type object.

'first check month name sql match system last month name     if cstr(monthname(lastmonth.month, false)) = cstr(dts.variables("monthnamefromsql").value)         msgbox("month name variable equals last month value")         if rcnt = 0             msg = "job returned 0 rows month " & cstr(monthname(lastmonth.month, false)) & " - check outpatient database operator previous month's data has been loaded database."         else             msg = "job returned " & rcnt & " rows - job finished"         end if     else     'put in code handle if month values not match     end if 

i replaced:

if cstr(monthname(lastmonth.month, false)) = cstr(dts.variables("monthnamefromsql").value)         msgbox("month name variable equals last month value") 

with:

if 1=1 msgbox("month name variable equals last month value") 

just check work simple comparing statement , code had in if statement, did not suspect msg variable populating, though error message.

so realise with:

if cstr(monthname(lastmonth.month, false)) = cstr(dts.variables("monthnamefromsql").value) 

the montnamefromsql populated result set of previous sql task, , in script using check against system month name. want check these values , if match success , move onto check rows returned, , populate message send in email task.

hopefully it's simple , fresh pair of eyes can spot!

thanks

andrew

either have wrong data types assigned (which error message stating) or have not faithfully reproduced code.

i created package 3 variables.

• emailmessage: string -
• monthnamefromsql: string - july
• rowcount: int32 - 0

i configured "scr works fine" task allow read access second 2 variables , read/write access emailmessage.

inside script, used code. ran original works , 1 doesn't work you.

public sub main()     dim monthfromsql string     dim lastmonth new date(datetime.today.year, datetime.today.month - 1, 1)     dim rcnt integer     dim msg string      'msgbox("month name sql " & cstr(dts.variables("monthnamefromsql").value))     rcnt = cint(dts.variables("rowcount").value)     'if rcnt = 0     '    msg = "job returned 0 rows month " & cstr(monthname(lastmonth.month, false)) & " - check database operator previous month's data has been loaded database."     'else     '    msg = "job returned " & rcnt & " rows - job finished"     'end if      'first check month name sql match system last month name     if cstr(monthname(lastmonth.month, false)) = cstr(dts.variables("monthnamefromsql").value)         msgbox("month name variable equals last month value")         if rcnt = 0             msg = "job returned 0 rows month " & cstr(monthname(lastmonth.month, false)) & " - check outpatient database operator previous month's data has been loaded database."         else             msg = "job returned " & rcnt & " rows - job finished"         end if     else         'put in code handle if month values not match         msg = "i here"     end if      'pass message variable value out used in message      dts.variables("emailmessage").value = msg      dts.taskresult = scriptresults.success end sub 

as can see value of emailmessage variable, it's populated , script has completed.

this package using sql server 2012 fundamentals of trying has been available since 2005.

• integration services (ssis) variables
• using variables in script task