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
