March 7, 2019 at 4:19 pm
Dear Forum,
I'm interested in having a database view that will let me avoid using the SSIS data conversion transformation component in an SSIS process.
Why? To simplify the SSIS process and put any complexity of the logic in the database (if that is a worthwhile aim ? which may be a question in itself .. ).
I wondered : Have other people studied what the SSIS data conversion transformation component is doing and then tried to move the logic/processing earlier into T-SQL and a view ?
i.e. I wondered if there is an equivalent try_convert(...) example for each of the target data types of the SSIS data conversion transformation component?
The source data is all in varchar(50) fields coming from a third party CSV.
The conversions of particular interest are conversion to these 4 data types:
double-precision float [DT_R8]
database timestamp [DT_DBTIMESTAMP]
four-byte signed integer [DT_I4]
currency [DT_CY]
The output table has almost all the columns as nvarchar(50) with only 3 columns defined as datetime unfortunately ..
i.e. we are storing date values and numeric values in nvarchar column which I know is not desirable ..
I have opportunity to change this table design in April and I will aim to switch columns intended to hold numeric to appropriate numeric data type and likewise with datetimes
The input values are as per:
Some dates are like "2018/07/09" and some are "2018/07/09 23:11:22" (and some dates are missing ... empty string ..)
Some of the numeric values incoming contain a single blank .. but the destination table (via the SSIS process with SSIS data conversion transformation component ) seems to change these single blank values to empty string ..
I'm trying to match the existing data in the destination table which has dates in a format like 2019-01-01 16:50:22 in the nvarchar(50) fields ..
Thus far I have come up with:
(for the true datetime field in destination table)
,TRY_CONVERT(datetime,[Call Date],111) as [Call Date]
for the datetime field in destination table held in nvarchar(50):
, format ( TRY_CAST(LEFT([Call Time (hms)] , 10) + ' ' + REPLACE(RIGHT([Call Time (hms)] ,8 ), '-', ':') AS DATETIME) , 'yyyy-MM-dd HH:mm:ss') as [Call Time (hms)]
Which just leaves me with these conversions to deal with:
double-precision float [DT_R8]
four-byte signed integer [DT_I4]
currency [DT_CY]
thanking you in advance !
I have noted that this example:
,TRY_CONVERT(integer,[Time Pickup]) as [Time Pickup]
turns an input of a single blank into a 0 .. .. where as I want empty sting output for a single blank input ..
This seems to work:
,TRY_CONVERT(integer,NULLIF(Rtrim([Time Pickup]),'')) as [Time Pickup]
Allan
March 8, 2019 at 7:39 am
Have you given thought to what you just tried to do with a conversion to Integer? You tried to use a string to hold what should probably be a NULL value. Integers can't be strings. Period. That's not negotiable. If you have a single blank, then you have no value, so the value is unknown, and thus the only logical value to use is NULL. You can always substitute a blank for a NULL when such an integer column is indeed NULL, provided you are also always converting that integer to a string to begin with. Just realize that integers can't store string characters like spaces. Ever. Just because your query works doesn't mean that SQL Server stored a blank in the column. It just doesn't work that way.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply