check if cast is possible

I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.I might try something like so:

Insert Into MyTable(MyDateField)
SELECT (CASE WHEN isDate(DateFieldTobeImported) = 1 Then DateFieldTobeImported ELSE Null End)
From TableBeingImported

If this does not work, please post some sample data.|||Thank you, it works.

Check condition and wait

Hi,

I have a data-flow-task that imports data to sqlserver.

Now I want to check, if a special column of an imported record is null.

If yes, I have to wait 10 minutes and jump to the data-flow-task again. (Cjeck and wait).

How can I do this with the integration services?

Thanks

Gerd

You can do timer style loops, so this could be extended for your loop on the 10 minute, and maybe use some extra variables to keep track.

For Loop Container Samples
(http://www.sqlis.com/310.aspx)

You could have an Exec SQL Task inside your loop as the first task. Query the column and assign a variable value using the query result to indicate if the column is true. Then have a Data Flow Task linked from the Exec SQL Task and use an Expression on the constraint such that it is satisfied only when the variable in indicates that the Exec SQL Task found a value.

The final thing would be to extend the loop EvalExpression such that if it would not wait/loop if the Exec SQL Task assigned variable indicate data had been found.

The principal seems sound, though you may want to adjust the expressions and variables used to fit exactly with what you want.

Char Trim

Hi,

I imported records from text file using BCP, in one column I am seeing blank space in starting as well in end. I printed the ASCII code it turn out as 32. But when I tried to trim with LTRIM and RTRIM it didn't work. I tried to replace the character using REPLACE witn no avail.

Please help.

Thanks.

You could try the following:

RIGHT(LEFT(ColumnName, LEN(ColumnName) - 1), LEN(ColumnName) - 2)

This will trim the leading and trailing characters.

Is there a chance you could you check the results of the following just to confirm that the ASCII value of the character is 32 ? :

SELECT TOP 1 ASCII(LEFT(ColumnName, 1))
FROM MyTable

Thanks

Chris

|||

I confirmed that the char is 32. I am still seeing the blanks before and after the values.

|||Is the column CHAR or VARCHAR or NVARCHAR?|||

As Dan states, the datatype is interesting here, but if you are using rtrim and ltrim, it shouldn't matter... Please post some sample code like this so we can get an idea of what you are really wanting...

drop table test
go
create table test
(
charValue char(10),
varcharValue varchar(10)
)
go
insert into test
select 'Test' + char(32) ,
'Test' + char(32)
go
select '*' + charValue + '*' as char1,
'*' + ltrim(rtrim(charValue)) + '*' as char2,
'*' + varcharValue + '*' as varchar1,
'*' + ltrim(rtrim(varcharValue)) + '*' as varchar2
from test
go
char1 char2 varchar1 varchar2
*Test * *Test* *Test * *Test*

|||

Louis,

I was surprised to see that your varcharValue included the trailing space. I thought VARCHAR columns removed trailing spaces. So then I went to study the references and found that it depends on ANSI_PADDING in the database.

http://msdn2.microsoft.com/en-us/library/ms175055.aspx

The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the ANSI_PADDING option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not truncated.

Thanks for the education! ;-)

|||Thanks for the added info...|||

Hi,

Thaks for your help.

Column is Varchar(50) also I am seeing following property of column (please note I am using SQLServer 2005 SP1).

TrimTrailingBlank =0

FixedLenNullinSource=0

This column is contains database sizes like 2000 MB

I need to remove the MB thing which I am doing

Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize)))

then I have to convert to Numeric type but when I am trying

Cast(ltrim(rtrim(Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize))))) as Numeric(20,2))

getting error

Msg 8114, Level 16, State 5, Line 3

Error converting data type varchar to numeric.

Thanks

|||

I think the computation for the third parameter of SUBSTRING is not correct. I think you just want "(CharIndex('M',DatabaseSize)-1)".

declare @.DatabaseSize varchar(50)
set @.DatabaseSize = ' 2000 MB '

print ' ' + cast(LEN(@.DatabaseSize) as varchar)
print ' ' + cast(CharIndex('M',@.DatabaseSize) as varchar)

print ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as Numeric(20,2))
print cast(len(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))) as varchar)
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as binary)

Dan

|||

Thanks a lot,

Reason I put Len() function cause we do have some rows where MB was missing so what I did I concanated 'MB' and use ChartIdex -1 and it is working now !!!! All along I was looking at wrong place to resolve the problem.

Cheers.