Data type validation

Hi all,

I want to make a conditional split based on the data type provided by the input.

For example : If the comming (Column x) is of data type (numeric) then pass , else do not pass.

(pass = Case 1

Do not pass = Case 2).

Is there any way for doing so ?

You can use conditional split transformation for this.

The following link will help you get started
http://technet.microsoft.com/en-us/library/ms137886.aspx

Thanks

|||

I think Data Convertion transform is the easiest way to do this, see this for details:

http://technet.microsoft.com/en-us/library/ms186792.aspx

You would read the data as string, then try to convert it to numeric. Configure Error Disposition to redirect row. The "pass" will go to default (green) output, the rows that do not pass will go to error output.

Data Type question

Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David
|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>

Data Type question

Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>

Data Type question

Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I often have the dilemma of whether to use Text type or Varchar type.
>> Normally the situation is that the user will input 50 characters however
>> in some cases he will input 2000 characters should I make the field Text
>> or Varchar(2000)
>> Technically the question is whether a varchar field is assigned the space
>> automatically or only on request also how significant is the overhead of
>> using the Text type
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>

Data type issues

One:

I can't map in the Lookup Transformer editor from my input (a string) that has been modded using the following in the Derived Column Transformer as follows:

Formatted_Date

((DT_DATE)(SUBSTRING([IN-DT-CYMD-TRANS],5,2) + "-" + SUBSTRING([IN-DT-CYMD-TRANS],7,2) + "-" + SUBSTRING([IN-DT-CYMD-TRANS],1,4)))

database_date (DT_DBDATE)

The destination field has the data type "datetime", according to Management Studio.

Two:

I have almost the exact same problem with another mapping, trying to cast a string to an int (INT is not one of the choices in my list...):

Batch_Code_As_Int

((DT_NUMERIC,1,1)([IN-BAT-NBR]))

numeric [DT_NUMERIC]

Any thoughts?

Thanks!

Jim Work

Jim Work wrote:

One:

I can't map in the Lookup Transformer editor from my input (a string) that has been modded using the following in the Derived Column Transformer as follows:

Make your derived column type a DB_TIMESTAMP and see if that works.

Jim Work wrote:

Two:

I have almost the exact same problem with another mapping, trying to cast a string to an int (INT is not one of the choices in my list...):

Batch_Code_As_Int

((DT_NUMERIC,1,1)([IN-BAT-NBR]))

numeric [DT_NUMERIC]

Try DT_I4 (a 32-bit integer)

Data Type in Parameter Mapping for an Execute SQL Task

Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.

The input parameter is define as @.Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:

SSIS package "DCLoading.dtsx" starting.
Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging

update DCA_HFStaging set
[dbo].[Control_ID] = P0 where [Control_ID] is null
" failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Control_ID
Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DCLoading.dtsx" finished: Failure.

Any help?this link might help: http://www.sqlis.com/default.aspx?58|||Thanks for the response. The link you posted is helpful, but does not include the example that I need. I want to set an Int32 variable as intput parameter, but I don't find any integer type in the parameter mapping. And, if I set the SSIS variable as string type, in the parameter mapping, I set the input parameter as varchar, it will return error said the parameter length can't be defined.