Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.|||You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.|||You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.
|||You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

error with datetime datatype

Hi Guys,

I'm having problem in loading textfile into the database. One of the columns in the textfile has a datetime datatype.

Here is a sample of the text file. All the other columns are string except for the datetime: "5/4/2006"

"1","1","ITEM","5/4/2006","10:05:04","11110",10004,"Regular Half Chicken",1,130.00,0,0

Error is shown below.

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [154]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Error: 0xC020901C at Data Flow Task, OLE DB Destination [154]: There was an error with input column "Transaction_Date" (1233) on input "OLE DB Destination Input" (167). The column status returned was: "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, OLE DB Destination [154]: The "input "OLE DB Destination Input" (167)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (167)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (154) 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Please help!!!

Thanks in advance,

Larry

You'll need to throw a "convert" into the dataflow to convert that date format:

select convert(datetime, '10/31/2006',101) 'returns 2006-10-31 00:00:00
select convert(datetime, [Transaction_Date],101)

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = FalseThen

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

EndIf

EndIf

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" 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.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" 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.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" 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.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.