Dealing with BAD Dates in SSIS
in [Programming SQL]
|
From: robboll on 25 Jun 2010 02:23 I know this isn't the correct group for this tool, but it belongs to SQL Server . . . so I have a simple table with five columns. One of the column is a date. When I import the data into a datetime field I get the error: An OLE DB error has occurred. Error code: 0x80004005. Description: "Invalid character value for cast specification". As it turns out the error occurs when it hits a date with this value "00/00/0000" or a bad date like "02/31/2010" So like with DTS transformations, I need to check to see if the value is a correct date format and if it is not, make it null before it is appended to SQL Server. I will appreciate any help with this one. Thank you! RBollinger
From: Bob Barrows on 25 Jun 2010 11:15 robboll wrote: > I know this isn't the correct group for this tool, but it belongs to > SQL Server . . . so > > I have a simple table with five columns. One of the column is a > date. > > When I import the data into a datetime field I get the error: An OLE > DB error has occurred. Error code: 0x80004005. Description: > "Invalid character value for cast specification". > > > As it turns out the error occurs when it hits a date with this value > "00/00/0000" or a bad date like "02/31/2010" > > > So like with DTS transformations, I need to check to see if the value > is a correct date format and if it is not, make it null before it is > appended to SQL Server. > I initially thought that SSIS had an IsDate() function, but I see I was mistaken: http://msdn.microsoft.com/en-us/library/ms141232.aspx Where is the data coming from? If it's a database, perhaps you can do this check in the query that retrieves the data. Otherwise, you will have to use a staging table and use a sql query with a CASE statement to insert the data into the real destination table. -- Bob Barrows
From: jm27102 on 25 Jun 2010 11:54 I just dealt with something very similar. Use the ISDATE() function to check if the date is valid. Redirect your "bad" rows to another table, and create a TSQL function to process, convert or correct the bad dates. "robboll" wrote: > I know this isn't the correct group for this tool, but it belongs to > SQL Server . . . so > > I have a simple table with five columns. One of the column is a > date. > > When I import the data into a datetime field I get the error: An OLE > DB error has occurred. Error code: 0x80004005. Description: > "Invalid character value for cast specification". > > > As it turns out the error occurs when it hits a date with this value > "00/00/0000" or a bad date like "02/31/2010" > > > So like with DTS transformations, I need to check to see if the value > is a correct date format and if it is not, make it null before it is > appended to SQL Server. > > I will appreciate any help with this one. > > Thank you! > > RBollinger > > > . >
From: jm27102 on 25 Jun 2010 12:22 IsDate() should be in SQL Server in "Other Functions" it returns a 1 or a 0 "Bob Barrows" wrote: > robboll wrote: > > I know this isn't the correct group for this tool, but it belongs to > > SQL Server . . . so > > > > I have a simple table with five columns. One of the column is a > > date. > > > > When I import the data into a datetime field I get the error: An OLE > > DB error has occurred. Error code: 0x80004005. Description: > > "Invalid character value for cast specification". > > > > > > As it turns out the error occurs when it hits a date with this value > > "00/00/0000" or a bad date like "02/31/2010" > > > > > > So like with DTS transformations, I need to check to see if the value > > is a correct date format and if it is not, make it null before it is > > appended to SQL Server. > > > > I initially thought that SSIS had an IsDate() function, but I see I was > mistaken: > http://msdn.microsoft.com/en-us/library/ms141232.aspx > > Where is the data coming from? If it's a database, perhaps you can do this > check in the query that retrieves the data. Otherwise, you will have to use > a staging table and use a sql query with a CASE statement to insert the data > into the real destination table. > > > -- > Bob Barrows > > > . >
From: Bob Barrows on 25 Jun 2010 12:44
Yes, I know. That's a T-SQL function which is not in the list of _SSIS Expression_ functions :-) That's why I suggested using a staging table so that he could use the T-SQL ISDATE() function in a CASE statement. jm27102 wrote: > IsDate() should be in SQL Server in "Other Functions" it returns a 1 > or a 0 > > "Bob Barrows" wrote: > >> robboll wrote: >>> I know this isn't the correct group for this tool, but it belongs to >>> SQL Server . . . so >>> >>> I have a simple table with five columns. One of the column is a >>> date. >>> >>> When I import the data into a datetime field I get the error: An >>> OLE >>> DB error has occurred. Error code: 0x80004005. Description: >>> "Invalid character value for cast specification". >>> >>> >>> As it turns out the error occurs when it hits a date with this value >>> "00/00/0000" or a bad date like "02/31/2010" >>> >>> >>> So like with DTS transformations, I need to check to see if the >>> value >>> is a correct date format and if it is not, make it null before it is >>> appended to SQL Server. >>> >> >> I initially thought that SSIS had an IsDate() function, but I see I >> was mistaken: >> http://msdn.microsoft.com/en-us/library/ms141232.aspx >> >> Where is the data coming from? If it's a database, perhaps you can >> do this check in the query that retrieves the data. Otherwise, you >> will have to use a staging table and use a sql query with a CASE >> statement to insert the data into the real destination table. >> >> >> -- >> Bob Barrows >> >> >> . -- Bob Barrows |