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. Con
nect
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:

> Good idea. One of the main purposes of SSIS is to scrub data before it is
> loaded.
>
> 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 conve
rt
> the input to the appropriate datatype and the second transformation assign
s
> 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. C
onnect
> 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 al
l 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...
>|||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...[vbcol=seagreen]
> Dan
> Thank you very very much...it worked a treat.
> Thanks for taking the time to post a reply.
> --
> Milk Bottle
>
> "Dan Guzman" wrote:
>