casting variables in SSIS
-
well here is my scenario:
I have defined 3 variables in SSIS
1. varJunkRC String
holds the value of execute sql task:
select cast(count(*) as char(50)) from junk
an execute sql task afterwards takes the value of varJunkRC variable and updates column DestRC in table Rcount. DestRC column is of datatype Int in SQL Server.
2. varNameBadRC Int32
holds the value of number of rows failed. value is stored in the variable by RowCount Transformation.
an execute sql task afterwards takes the value of varNameBadRC variable and updates column BadRC in table Rcount. BadRC column is of datatype Int in SQL Server.
3. varNameRC String
holds the value of execute sql task:
select to_char(count(*)) from dbanasir.name
an execute sql task afterwards takes the value of varNameRC variable and updates column SourceRC in table Rcount. SourceRC column is of datatype Int in SQL Server.
all I am trying to do here is get the recordcount of source table, destination table, and count of failed number of rows and then if count of source table records = destination table count + error record count then
Dts.TaskResult = Dts.Results.Success
else
Dts.TaskResult = Dts.Results.Fail
my problem is that 2 of my variables are string data types. I dont see any cast function that I can use to convert string datatype to numeric in SSIS. I looked at this link:
http://technet.microsoft.com/en-us/library/ms141704.aspx
is there a way to cast string variable in ssis to numeric? if not whats the way for me to get what I want?
since I am storing the value of each variable in an int data type in sql server I can use that value as well. but not sure how can I query sql server and get value in script task?
thanks
-
Well, for starters, I'm not sure why you're working with strings to begin with. You're doing a count(*) and converting it to varchar and I'm not sure why. Why not keep it as int, and just define your var in SSIS as an int32? I don't get what the purpose of that is.
Getting past that though, to convert back to an int, you need to either use the convert transform or the derived col, or convert it by hand in a script task. The function you're looking for is Cint(var). And if you go into any expression editor in SSIS, like the one found in the derived column transform, you'll see a list of functions that you can use to convert. It's all fairly straightforward.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
well what I found out that I can convert variables to int using CInt. so I used that. but now running into another problem. When I try to compare the variable the script task doesnt like it. it complaints on the code in red. All the variables are listed in the readwrite variables list. Msgbox returns what I expect. but when it get to IF statement it seems like doesnt like my variables.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.Variables("varSourceCount").Value = CInt(Dts.Variables("varNameBadRC").Value) + CInt(Dts.Variables("varJunkRC").Value)
Dts.Variables("varDestCount").Value = CInt(Dts.Variables("varNameRC").Value)
MsgBox(Dts.Variables("varDestCount").Value)
If Dts.Variables("varDestCount").Value > Dts.Variables("varSourceCount").Value Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
what am i doing wrong here? the script task doesnt compile. all the packages have package level scope. varDestCount and varSourceCount are both of type int32.
if I remove the if-end if block the task compiles fine and runs fine as well. am i using the wrong way to compare these variables.
-
I don't know dude... I just created your same structure and it worked just fine for me. I used your code, your var names, everything. And it works just fine for me.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply