September 2, 2010 at 11:00 am
Hello,
This is my first time using the Forums so if I am not clear on what I am asking please excuse me.
I have the following list of files that I am looping through (ForEach Loop) and passing into a variable called strFileName.
20100830_*_user.txt
20100831_*_user.txt
20100901_*_user.txt
20100902_*_user.txt
I have the sql query (below) that passes the results into another parameter called TrxDate. (The result of this query is a date stored as a string e.x. 20100902)
select cast(left(CONVERT(CHAR(8), getdate(), 112),4) +
substring(CONVERT(CHAR(8), getdate(), 112),5,2) +
substring(CONVERT(CHAR(8), getdate(), 112),7,2) as varchar(8)) as TrxDate
Within my Flat File Connection Manager I am using Expression Builder, and trying to do the following.
SUBSTRING( @[User::strFileName] , 1, 8) == @[User::TrxDate]
The error that I get is: Cannot Convert Expression value to property type.
'System.Boolean' to 'System.String'
I tried this too:
SUBSTRING( (DT_WSTR, 8) @[User::strFileName] , 1, 8) == (DT_WSTR, 8) @[User::TrxDate]
I was guessing this would select the appropriate file if the left 8 of the file was equal to the SQL date, but my logic maybe wrong?
Any help or direction somebody could give me that would be great. 🙂
September 3, 2010 at 11:06 am
Value assigment only uses one equal sign. But I think in the expression builder you can only set the full value to another value, not part of the value like you are trying to do with the SUBSTRING and you don't need an equal sign. For example to set the ConnectionString of a file to a dated name, where @[User::WorkPath] is the path of to the files, this is what you put in the Expression builder:
@[User::WorkPath] + "\\" +(DT_STR,4,1252)Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + (DT_STR,4,1252) DatePart("yyyy",getdate())+ "_text_file_name.txt"
This evaluates to:
\\my path\09032010_text_file_name.txt
HTH,
Marcy
September 3, 2010 at 11:11 am
Thanks Marcy! This gives me a great example to work from, and you have given me a good idea of how the Expression Builder works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply