Databases
StackOverflow2013
Postsdbo
POSSIS Script Task Debugging When Checking Value Comparison
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSIS Script Task Debugging When Checking Value Comparison
    primarykey
    data
    text
    <p>I have this code in a script task:</p> <pre><code>Public Sub Main() ' ' Add your code here ' Dim MonthFromSQL As String Dim lastMonth As New Date(DateTime.Today.Year, DateTime.Today.Month - 1, 1) Dim rcnt As Integer Dim msg As String 'MsgBox("Month name from SQL is " &amp; CStr(Dts.Variables("MonthNameFromSQL").Value)) rcnt = CInt(Dts.Variables("RowCount").Value) If rcnt = 0 Then msg = "Job returned 0 rows for month " &amp; CStr(MonthName(lastMonth.Month, False)) &amp; " - check with database operator that previous month's data has been loaded into the database." Else msg = "Job returned " &amp; rcnt &amp; " rows - Job Finished" End If 'Pass message variable value out to be used in message Dts.Variables("EmailMessage").Value = msg Dts.TaskResult = Dts.Results.Success End Sub </code></pre> <p>If I modify the IF statement to below to be able to check the month value from SQL DB against system month value it gives me a DTS script error saying that:</p> <blockquote> <p>The type of the value being assigned to variable "User::EmailMessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.</p> </blockquote> <pre><code>'First check month name from sql match system last month name If CStr(MonthName(lastMonth.Month, False)) = CStr(Dts.Variables("MonthNameFromSQL").Value) Then MsgBox("Month name variable equals last month value") If rcnt = 0 Then msg = "Job returned 0 rows for month " &amp; CStr(MonthName(lastMonth.Month, False)) &amp; " - check with outpatient database operator that previous month's data has been loaded into the database." Else msg = "Job returned " &amp; rcnt &amp; " rows - Job Finished" End If Else 'Put in code to handle if month values do not match End If </code></pre> <p>I replaced:</p> <pre><code>If CStr(MonthName(lastMonth.Month, False)) = CStr(Dts.Variables("MonthNameFromSQL").Value) Then MsgBox("Month name variable equals last month value") </code></pre> <p>With:</p> <pre><code>If 1=1 Then MsgBox("Month name variable equals last month value") </code></pre> <p>Just to check it would work with a simple comparing statement and that it was something to do with the code I had in the IF statement, as I did not suspect it was anything to do with the msg variable I was populating, even though this was the error message.</p> <p>So I realise it is something to do with:</p> <pre><code>If CStr(MonthName(lastMonth.Month, False)) = CStr(Dts.Variables("MonthNameFromSQL").Value) Then </code></pre> <p>The montNameFromSQL is populated with the result set of a previous SQL task, and then in this script I am using it to check against the system month name. I want to check these values and if a match then success and move onto to check the rows returned, and populate the message to send in my email task.</p> <p>Hopefully it's something simple and a fresh pair of eyes can spot!</p> <p>Thanks</p> <p>Andrew</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrew
    1. USAndrew
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COwhat is the data type of Dts.Variables("MonthNameFromSQL").Value? is it object type? Also show sql script which used in the sql task for populating the montNameFromSQL variable.
      singulars
      1. POSSIS Script Task Debugging When Checking Value Comparison
      1. USS.M
    2. CONo data type is string rather than object. The query below is used to get the value of monthName (the alias in query) into MonthNameFromSQL using result set property in sql task :select distinct convert(varchar(12),(datename(month, date_column))) as monthName FROM dbo.table1
      singulars
      1. POSSIS Script Task Debugging When Checking Value Comparison
      1. USAndrew
    3. COAfter changing the statement to If 1=1 Then MsgBox("Month name variable equals last month value"), was the error remained the same?
      singulars
      1. POSSIS Script Task Debugging When Checking Value Comparison
      1. USS.M
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload