Derived Column

How do i change the data type of a column using the derived column feature?
I have two columns that need changing
(a) needs to be changed to an integer
(b) needs to be changed to a date
Both columns contain balnks and spaces!
Realise i could do this using a view but is it best practice to change the
data types before they get into the Database
--
Milk Bottle> Realise i could do this using a view but is it best practice to change the
> data types before they get into the Database
Good idea. One of the main purposes of SSIS is to scrub data before it is
loaded.
> I have two columns that need changing
> (a) needs to be changed to an integer
> (b) needs to be changed to a date
One method is with 4 Derived Column transformations, 2 for the integer and 2
for the datetime. The first transformation of each pair attempts to convert
the input to the appropriate datatype and the second transformation assigns
a default value if a conversion error occurs. To do this:
Create 2 new derived column transforms for the integer field and specify
expression as (DT_I4)[YourIntegerData] in the first transformation. Connect
that derived column output path to a new Union All and the derived column
error path (redirect row) to the second transformation that specifies your
desired default value (e.g. (DT_I4)0). Connect that derived column output
path to the same union all as the first transformation so that all records
are processed and contain valid integer values.
Repeat the above process for the datetime field with the union all output of
the integer transforms connected to a derived column with expression
(DT_DBTIMESTAMP)[YourDateTimeData]. The output of the second union all will
then contain only valid integer and datetime values.
Hope this helps.
Dan Guzman
SQL Server MVP
"MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
news:488C7D26-B9A6-43F8-BB9D-596EF19B2517@.microsoft.com...
> How do i change the data type of a column using the derived column
> feature?
> I have two columns that need changing
> (a) needs to be changed to an integer
> (b) needs to be changed to a date
> Both columns contain balnks and spaces!
> Realise i could do this using a view but is it best practice to change the
> data types before they get into the Database
>
>
> --
> Milk Bottle|||Dan
Thank you very very much...it worked a treat.
Thanks for taking the time to post a reply.
--
Milk Bottle
"Dan Guzman" wrote:
> > Realise i could do this using a view but is it best practice to change the
> > data types before they get into the Database
> Good idea. One of the main purposes of SSIS is to scrub data before it is
> loaded.
> > I have two columns that need changing
> > (a) needs to be changed to an integer
> > (b) needs to be changed to a date
> One method is with 4 Derived Column transformations, 2 for the integer and 2
> for the datetime. The first transformation of each pair attempts to convert
> the input to the appropriate datatype and the second transformation assigns
> a default value if a conversion error occurs. To do this:
> Create 2 new derived column transforms for the integer field and specify
> expression as (DT_I4)[YourIntegerData] in the first transformation. Connect
> that derived column output path to a new Union All and the derived column
> error path (redirect row) to the second transformation that specifies your
> desired default value (e.g. (DT_I4)0). Connect that derived column output
> path to the same union all as the first transformation so that all records
> are processed and contain valid integer values.
> Repeat the above process for the datetime field with the union all output of
> the integer transforms connected to a derived column with expression
> (DT_DBTIMESTAMP)[YourDateTimeData]. The output of the second union all will
> then contain only valid integer and datetime values.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
> news:488C7D26-B9A6-43F8-BB9D-596EF19B2517@.microsoft.com...
> >
> > How do i change the data type of a column using the derived column
> > feature?
> > I have two columns that need changing
> > (a) needs to be changed to an integer
> > (b) needs to be changed to a date
> > Both columns contain balnks and spaces!
> >
> > Realise i could do this using a view but is it best practice to change the
> > data types before they get into the Database
> >
> >
> >
> >
> >
> > --
> > Milk Bottle
>|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
news:3FAD3449-D418-46FB-83CC-1ED98F1A6F1C@.microsoft.com...
> Dan
> Thank you very very much...it worked a treat.
> Thanks for taking the time to post a reply.
> --
> Milk Bottle
>
> "Dan Guzman" wrote:
>> > Realise i could do this using a view but is it best practice to change
>> > the
>> > data types before they get into the Database
>> Good idea. One of the main purposes of SSIS is to scrub data before it
>> is
>> loaded.
>> > I have two columns that need changing
>> > (a) needs to be changed to an integer
>> > (b) needs to be changed to a date
>> One method is with 4 Derived Column transformations, 2 for the integer
>> and 2
>> for the datetime. The first transformation of each pair attempts to
>> convert
>> the input to the appropriate datatype and the second transformation
>> assigns
>> a default value if a conversion error occurs. To do this:
>> Create 2 new derived column transforms for the integer field and specify
>> expression as (DT_I4)[YourIntegerData] in the first transformation.
>> Connect
>> that derived column output path to a new Union All and the derived column
>> error path (redirect row) to the second transformation that specifies
>> your
>> desired default value (e.g. (DT_I4)0). Connect that derived column
>> output
>> path to the same union all as the first transformation so that all
>> records
>> are processed and contain valid integer values.
>> Repeat the above process for the datetime field with the union all output
>> of
>> the integer transforms connected to a derived column with expression
>> (DT_DBTIMESTAMP)[YourDateTimeData]. The output of the second union all
>> will
>> then contain only valid integer and datetime values.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
>> news:488C7D26-B9A6-43F8-BB9D-596EF19B2517@.microsoft.com...
>> >
>> > How do i change the data type of a column using the derived column
>> > feature?
>> > I have two columns that need changing
>> > (a) needs to be changed to an integer
>> > (b) needs to be changed to a date
>> > Both columns contain balnks and spaces!
>> >
>> > Realise i could do this using a view but is it best practice to change
>> > the
>> > data types before they get into the Database
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Milk Bottlesql