integer variable usage in sqlStatement expression

I have an integer variable which i had to cast as a string in order for it to show as an integer in the sql expression which is a string in order for it to INSERT as an integer? Read that 5 times fast!!!

"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @.[User:aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
1,
"+(DT_STR, 4, 1252)@.[User::rcSourceFile]+",
"+(DT_STR, 4, 1252)@.[User::rcCleanInserts]+",
"+(DT_STR, 4, 1252)@.[User::rcDataConversionErrors]+",
"+(DT_STR, 4, 1252)@.[User::rcPKViolation]+",
"+(DT_STR, 4, 1252)@.[User::rcExistingInvoiceID]+",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase1] +",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase2] +",
"+(DT_STR, 4, 1252)@.[User::rcConditionalSplitErrors]+"
)"

all the variable beginning with rc are Int32 variables being loaded using the Row Count task. they are being inserted into an integer field in the table. Is this whack or what?

I was trying to cast is using DT_I4 but couldn't find any samples nor could I get it to work. The above is successful!!!

jpsR1 wrote:

I have an integer variable which i had to cast as a string in order for it to show as an integer in the sql expression which is a string in order for it to INSERT as an integer? Read that 5 times fast!!!

"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @.[User:aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
1,
"+(DT_STR, 4, 1252)@.[User::rcSourceFile]+",
"+(DT_STR, 4, 1252)@.[User::rcCleanInserts]+",
"+(DT_STR, 4, 1252)@.[User::rcDataConversionErrors]+",
"+(DT_STR, 4, 1252)@.[User::rcPKViolation]+",
"+(DT_STR, 4, 1252)@.[User::rcExistingInvoiceID]+",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase1] +",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase2] +",
"+(DT_STR, 4, 1252)@.[User::rcConditionalSplitErrors]+"
)"

all the variable beginning with rc are Int32 variables being loaded using the Row Count task. they are being inserted into an integer field in the table. Is this whack or what?

I was trying to cast is using DT_I4 but couldn't find any samples nor could I get it to work. The above is successful!!!

So, what is your problem?

The expression looks right and makes sense. The expression is treated as an string; so yes, you have to cast all non-string variables to string in order to be used in the expression.

|||Right, well, you can't concatenate (a string operator) a string with an integer data type. So what you've done is correct.|||You can save some keystrokes by casting to (DT_WSTR,length) instead, though.|||It's a little confusing i have an Integer stored in a Package Variable set as an Int32 that I have to type cast as a string in order to show as an integer? Is it only me that sees this as whacky?|||

This would be more intuitive:

"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @.[User:aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
1,
"+@.[User::rcSourceFile]+",
"+@.[User::rcCleanInserts]+",
"+@.[User::rcDataConversionErrors]+",
"+@.[User::rcPKViolation]+",
"+@.[User::rcExistingInvoiceID]+",
"+@.[User::rcISNULLCase1] +",
"+@.[User::rcISNULLCase2] +",
"+@.[User::rcConditionalSplitErrors]+"
)"

|||

jpsR1 wrote:

It's a little confusing i have an Integer stored in a Package Variable set as an Int32 that I have to type cast as a string in order to show as an integer? Is it only me that sees this as whacky?

But you can't concatenate an integer to a string. It has nothing to do with SSIS. It's an integer. You're trying to concatenate. Integers can't be concatenated. End result: you have to cast your integers to strings.|||

Why do you think I'm trying to concatenate an integer?

I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.

|||

jpsR1 wrote:

Why do you think I'm trying to concatenate an integer?

I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.

What do you think the "+" symbol means when you are assembling your SQL statement?|||

jpsR1 wrote:

Why do you think I'm trying to concatenate an integer?

I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.

You are right; the column in the table and the variable are integer; BUT the expression property of an SSIS object expects an string value; hence everything on it has to be an string.

|||

jpsR1 wrote:

Why do you think I'm trying to concatenate an integer?

I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.

Maybe this will help explain it:

You are assembling a string (the INSERT statement). If you go into SSMS, open a query editor, and type

Code Snippet

SELECT 'INSERT INTO TestTable VALUES(' + 1 +')'

you will get an error back because SQL Server can't add an integer to a string. The "+" has two functions. For numbers it is used for addition. For strings, it is used for concatenation. If you include a number and a string in the same expression, SSIS (and SQL Server, and most other programming tools) throws an error because you are doing something ambiguous. Do you want to concatenate, or do you want to add? The tools could guess (like VB 1 - 6) and implicitly convert the numbers to string values, but this is generally accepted as a bad practice, as it leads to bugs and unclear code. Instead, most newer development tools don't do implicit conversions (or at least have it disabled by default).