Prev: Who Created a Table
Next: sys.sql_expression_dependencies bug?
From: Adalberto Andrade on 4 Dec 2008 14:09
Hi

My problem is : I need converting a column with data type nvarchar (in SQL
SERVER 2000) with format “ddmmyyyy” to other column with data type datetime
(in SQL SERVER 2005) also with format “ddmmyyyy”.

The scene is : There is a OLE DB Source, where in SQL Command text box we
have”select dtemissao from testedata”. In external column and output column
we have the name “dtemissao”.
In derived column : Derived column name = dtemissao_new, Derived column =
<add as new column> and expression =
“(DT_DATE)(SUBSTRING((DT_STR,8,1252)dtemissao,1,2) + "-" +
SUBSTRING((DT_STR,8,1252)dtemissao,3,2) + "-" +
SUBSTRING((DT_STR,8,1252)dtemissao,5,4))”
And the data type = date[DT_DATE]

And the message error is : “[Derived Column [258]] Error: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (258)"
failed because error code 0xC0049064 occurred, and the error row disposition
on "output column "dtemissao_new" (273)" specifies failure on error. An error
occurred on the specified object of the specified component. There may be
error messages posted before this with more information about the failure.”

“[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The
ProcessInput method on component "Derived Column" (258) failed with error
code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error is
fatal and will cause the Data Flow task to stop running. There may be error
messages posted before this with more information about the failure.”

And finally I have an OLE DB Destination that dtemissao_new point to
sqlserver2005 column.

Any idea ?
Any help will be appreciate.
Best Regards
Adalberto Andrade

From: Jeremy Holovacs on 4 Dec 2008 15:00
The datetime type is not a string subtype, and therefore does not have a
format associated with it, per se. It is numeric by nature. That aside, it
looks like you're trying to implicitly convert it, but your format is not
right?

Can you try to cast:
SUBSTRING((DT_STR,8,1252)dtemissao,5,4) + "-" +
SUBSTRING((DT_STR,8,1252)dtemissao,3,2) + "-" +
SUBSTRING((DT_STR,8,1252)dtemissao,1,2)

to a DATETIME instead? this should put it in YYYY-MM-DD format, which could
be less confusing for the parser.
--
SELECT w.[name]
FROM women w
WHERE
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC;


"Adalberto Andrade" wrote:

> Hi
>
> My problem is : I need converting a column with data type nvarchar (in SQL
> SERVER 2000) with format “ddmmyyyy” to other column with data type datetime
> (in SQL SERVER 2005) also with format “ddmmyyyy”.
>
> The scene is : There is a OLE DB Source, where in SQL Command text box we
> have”select dtemissao from testedata”. In external column and output column
> we have the name “dtemissao”.
> In derived column : Derived column name = dtemissao_new, Derived column =
> <add as new column> and expression =
> “(DT_DATE)(SUBSTRING((DT_STR,8,1252)dtemissao,1,2) + "-" +
> SUBSTRING((DT_STR,8,1252)dtemissao,3,2) + "-" +
> SUBSTRING((DT_STR,8,1252)dtemissao,5,4))”
> And the data type = date[DT_DATE]
>
> And the message error is : “[Derived Column [258]] Error: SSIS Error Code
> DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (258)"
> failed because error code 0xC0049064 occurred, and the error row disposition
> on "output column "dtemissao_new" (273)" specifies failure on error. An error
> occurred on the specified object of the specified component. There may be
> error messages posted before this with more information about the failure.”
>
> “[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The
> ProcessInput method on component "Derived Column" (258) failed with error
> code 0xC0209029. The identified component returned an error from the
> ProcessInput method. The error is specific to the component, but the error is
> fatal and will cause the Data Flow task to stop running. There may be error
> messages posted before this with more information about the failure.”
>
> And finally I have an OLE DB Destination that dtemissao_new point to
> sqlserver2005 column.
>
> Any idea ?
> Any help will be appreciate.
> Best Regards
> Adalberto Andrade
>
From: Adalberto Andrade on 5 Dec 2008 07:19
Hi Jeremy Holovacs,

Thanks a lot, but unfortunately it didn't work.

The internal structure and how it exactly run I would like to know. I
don't know how the date format is implemented. But I need to solve it, so I
tried your advice and the message error was :

[Derived Column [514]] Error: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "component "Derived Column" (514)" failed because error code
0xC0049064 occurred,
and the error row disposition on "output column "dtemissao_new" (528)"
specifies failure
on error. An error occurred on the specified object of the specified
component.
There may be error messages posted before this with more information
about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The
ProcessInput
method on component "Derived Column" (514) failed with error code
0xC0209029.
The identified component returned an error from the ProcessInput
method.
The error is specific to the component, but the error is fatal and
will cause
the Data Flow task to stop running. There may be error messages
posted before this
with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread
"WorkThread0" has
exited with error code 0xC0209029. There may be error messages posted
before this with
more information on why the thread has exited.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "OLE DB Source" (199) returned
error code 0xC02020C4. The component returned a failure code when the
pipeline engine called PrimeOutput(). The meaning of the failure
code is
defined by the component, but the error is fatal and the pipeline
stopped executing.
There may be error messages posted before this with more
information about
the failure.


"Jeremy Holovacs" wrote:

> The datetime type is not a string subtype, and therefore does not have a
> format associated with it, per se. It is numeric by nature. That aside, it
> looks like you're trying to implicitly convert it, but your format is not
> right?
>
> Can you try to cast:
> SUBSTRING((DT_STR,8,1252)dtemissao,5,4) + "-" +
> SUBSTRING((DT_STR,8,1252)dtemissao,3,2) + "-" +
> SUBSTRING((DT_STR,8,1252)dtemissao,1,2)
>
> to a DATETIME instead? this should put it in YYYY-MM-DD format, which could
> be less confusing for the parser.
> --
> SELECT w.[name]
> FROM women w
> WHERE
> w.husband_id IS NULL
> ORDER BY
> w.hotness_factor DESC;
>
>
> "Adalberto Andrade" wrote:
>
> > Hi
> >
> > My problem is : I need converting a column with data type nvarchar (in SQL
> > SERVER 2000) with format “ddmmyyyy” to other column with data type datetime
> > (in SQL SERVER 2005) also with format “ddmmyyyy”.
> >
> > The scene is : There is a OLE DB Source, where in SQL Command text box we
> > have”select dtemissao from testedata”. In external column and output column
> > we have the name “dtemissao”.
> > In derived column : Derived column name = dtemissao_new, Derived column =
> > <add as new column> and expression =
> > “(DT_DATE)(SUBSTRING((DT_STR,8,1252)dtemissao,1,2) + "-" +
> > SUBSTRING((DT_STR,8,1252)dtemissao,3,2) + "-" +
> > SUBSTRING((DT_STR,8,1252)dtemissao,5,4))”
> > And the data type = date[DT_DATE]
> >
> > And the message error is : “[Derived Column [258]] Error: SSIS Error Code
> > DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (258)"
> > failed because error code 0xC0049064 occurred, and the error row disposition
> > on "output column "dtemissao_new" (273)" specifies failure on error. An error
> > occurred on the specified object of the specified component. There may be
> > error messages posted before this with more information about the failure.”
> >
> > “[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The
> > ProcessInput method on component "Derived Column" (258) failed with error
> > code 0xC0209029. The identified component returned an error from the
> > ProcessInput method. The error is specific to the component, but the error is
> > fatal and will cause the Data Flow task to stop running. There may be error
> > messages posted before this with more information about the failure.”
> >
> > And finally I have an OLE DB Destination that dtemissao_new point to
> > sqlserver2005 column.
> >
> > Any idea ?
> > Any help will be appreciate.
> > Best Regards
> > Adalberto Andrade
> >
 | 
Pages: 1
Prev: Who Created a Table
Next: sys.sql_expression_dependencies bug?