Casting DT_WSTR to integers in SSIS

Hello, all.

Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?

(DT_UI4) (SUBSTRING(@.[User::FullDataPath], LEN(@.[User:ataPath]) + 1, 2))

I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:\something\something\2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.

Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.

Thanks for any help.

I wonder if it doesn't like a period in the data when it tries to cast to an integer....

substring("c:\something\something\2.txt",LEN("c:\something\something") + 1, 2) = "2."|||I see that's not the case, as what I wrote.

Workaround: add a second derived column and in that component, do your cast to DT_UI4. In the first, just perform the substring operation.

I'm not sure that it will allow it in one step because it can't parse the data to determine if it's numeric or not based on the substring operation. That is, when trying to convert the output of substring, you have no idea if it's numeric or not. (YOU do, but SSIS doesn't) When you add a second derived column component, casting a column to DT_UI4 should be straightforward because SSIS is going to imply that it's numeric and can be cast.

I don't like it, but that's my guess. You are always welcome to vent this issue over at http://connect.microsoft.com/sqlserver/feedback.

Phil|||

What's the error message?

-Jamie

|||

Hey, Phil.

Thanks for the response. It turns out that my problem was relying on ALL of my variables being populated at runtime. I didn't bother to specify default values for the variables I had added to my package, because I was populating these variables are runtime. This was working, but once I started using those variables for cast operations, the Expression Builder evaluation mechanism was choking.

What I was doing was this:

1. I was getting a variable folder path from a database and storing that in a variable.

2. I used that folder path variable as an expression for the Foreach Loop Container, wherefrom I derived a full file path (spec) in order to process multiple files.

3. But then I got the brilliant idea of using the folder path and the full file spec to derive a numerical value (based on the file name) and I used that value as an input into an Execute SQL Task to do an auxiliary task.

The bottom line is that once I specified bogus default values at design time (which values were replaced anyway at runtime) for the variables upon which my cast expression depended, the Expression Builder evaluation mechanism was able to validate my cast expressions.

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

|||

phanoteus wrote:

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

Indeed. So I think you're beig a bit harsh on yourself criticising your actions as idiocy. It doesn't strike me as sensible or intuitive that variables have to be initialised to a value that will never get used. But that's just me.

-Jamie

casting date stored as integers

Hi
I have a SQL server database that have dates stored like 12142006003423 which means 2006-12-14 00:34:00
is there any way to cast it directly to a datetime type (I don't mean a user defined function as I've already implemented one)

thanks

Eisa:

Did you deliberately truncate the seconds out? And if so do you want the seconds truncated or rounded?

|||no,
it is just a COINCIDENCE|||

Eisa:

Here is a select statement to do this; however, it is a better option to have a front-end application do this formatting rather than have SQL Server perform this formatting:

declare @.dateTime bigint
declare @.dateTime2 bigint
set @.dateTime = 12142006003423
set @.dateTime2 = 8012006010203

select @.dateTime as [Date / Time],
convert (char(4), (@.dateTime/1000000)%10000) + '-' +
right ('0'+convert(varchar(2), (@.dateTime/cast(1000000000000 as bigint))%100), 2) + '-' +
right ('0'+convert(varchar(2), (@.dateTime/cast(10000000000 as bigint))%100), 2) + ' ' +
right ('0'+convert(varchar(2), (@.dateTime/10000)%100), 2) + ':' +
right ('0'+convert(varchar(2), (@.dateTime/100)%100), 2) + ':' +
right ('0'+convert(varchar(2), @.dateTime%100), 2)
as formattedDate

-- Output:

-- Date / Time formattedDate
-- -- -
-- 8012006010203 2006-08-01 01:02:03
-- 12142006003423 2006-12-14 00:34:23

|||Mugambo,
thanks for your interest. however I was looking for a direct cast or convert statement that can accomplish this task
I've created a scalar function for the ease of use as follows
CREATE FUNCTION Int2Date ( @.sdate bigint)
returns datetime as
begin
declare @.dt as varchar(14)
declare @.dd varchar(2)
declare @.mm varchar(2)
declare @.yy varchar(4)
declare @.hh varchar(2)
declare @.mi varchar(2)
declare @.ss varchar(2)
declare @.result varchar(25)

set @.dt = cast(@.sdate as varchar(14))
set @.mm = substring(@.dt,1,2)
set @.dd = substring(@.dt,3,2)
set @.yy = substring(@.dt,5,4)
set @.hh = substring(@.dt,9,2)
set @.mi = substring(@.dt,11,2)
set @.ss = substring(@.dt,13,2)

set @.result = cast (@.mm + '/' + @.dd + '/'+ @.yy + ' ' + @.hh + ':' + @.mi +':' + @.ss as datetime)
return @.result
end

In Oracle a to_date(8012006010203,'DDMMYYYYHHMISS') can do this in a single step i was wondering if SQL server have a similar function

thanks anyway