Welcome to blogs.conchango.com

Very Good example ! This blog is very good presented !

Thanks you so much Jamie !

It makes sense to me. So I spent most of the day trying to get it to work.

I decided it was simpler to construct the new filename for the FileRename task in a script. It took me a while to discover that even  though the variables had package scope they had to be passed to the script explicitly. Now I enter the FileSystemTask with the RenameFile selected and I'm getting errors because it cannot lock onto the new filename its supposed to be writing!

I am certain that my new name is getting passed from the script to the FileSystemTask  because I can see it in the output window. Why does it want to "lock" on to the new file? I must be confused. Why does it want two connections if it is a FileRename? Okay where do I tell it what the new name is if I don't put it in the connection?

Thanks for reading. Its late and its Friday.

First I create one package in SSIS and that ran successfully from the aspx page.

Using SSIS package wizard ,I read the textfile to Sql table .It works fine.But Through the aspx page ,I want to give the filename as a connection string .Is it Possible.Please help me.

I used SSIS wizard to import text file to table in database but this wizard ask for fixed source file and I want it to be variable. Please help me on my code in .NET or may be a stored procedure in SQL on how to pass in the package a variable source file. Nid help. Thanks.

If you are trying to find "EvaluateAsExpression" it is on the properties tab, not the Variables window.  You can get to it by pressing F4 if you don't already have it popping up as needed.  

I wasted hours trying to find it because I had it shrunk to a single line.  Looking through BOL, web pages, and many other places just kept leading me back to set "EvaluateAsExpression" = True.  But it wasn't plain where to set it.

Looking back, it was plain as day.  Once frustrated, the obvious becomes hidden.

Good post Jamie

Thanks.

Hey Jamie, Good post man.

Also thanks of Hellman for sharing your experience, I was also set to go to the place where "EvaluateAsExpression=True" was like finding a unicorn :). I am good now. Thanks.

FP

This was great information! However, I have one more question instead of showing a 4 digit year I need a 2 digit year for my file name. Any sugguestions how to?

RIGHT("0" + (DT_STR, 2, 1252)  MONTH( GETDATE()  ),

2) + RIGHT("0" + (DT_STR, 2, 1252)  DAY( GETDATE()  ), 2) + (DT_STR, 4,

1252)  YEAR( GETDATE()  ) + ".xls"

PERFECT!!!! Thank you Jamie for the help, and for such a rapid response.

How would you generate a dynamic file name containing a month name eg 3Oct2007

Hi Zaahid,

Look into the CONVERT function when passing a datetime value. You can optionally specify a style parameter. Style value 6 returns something very close to what you want:

select convert(varchar(30), getdate(), 6)

Brian

Hi Jamie,

Any ideas how to use an expression to generate the following format?

XYZ Audit - September 25th 2007

Of course I am pursuing whether the format can be changed first, but in the event it cannot, any suggestions? I did not see a convert style that matches this (with both full month and st/nd/rd/th suffixes). Mabye I will need to roll my own on this one.

Brian

Hi Jamie,

I have a datetime column being made in a derived colum. I want to use an expression in the derived column to get the current date, i.e. GETDATE(), but need to strip the time value off of the date. How would one do that? i.e. just return TODAY().

I also will have a couple other places i would like to strip the time so a generalized "time stripping" expression in addition to the TODAY expression would be good to know.

Thanks!

Chris

Thanks jamie!

That sure is a pain to get each value (month,day,year) of a date type and then concatenate them together into a string then cast them back to a datetime type. You would have thought they (MS) would have made this easier.

I agree with Chris about the pain of string manipulation.

My hope is that in the next SP there would be something like a FormatExpression property for variables in addition to the standard Expression property. It would take a format string (like String.Format) and comma delimited list of variables.  

This would make the date expressions above so much easier to create and debug.

And like String.Format it would use the default code page too. I wonder how many people have hard-coded 1252 into their packages?

Hi Jamie. Can you help me with my problem too?

The format of the files is yyyymmdd_MainFile.csv

Now I need to move all the generated files for last month.

How can I do that? If I use the code you posted on top, the program searches for files that matches the specified yyyymmdd. But I'm trying to process a whole month, how should I modify your code?

Hi Jamie

Why is this line not working?

@FileDate + * + @FileName

I want it to return files that satisfies the expression at @FileDate and @FileName regardless of what is in between. I'm not familiar with SQL 2005 SSIS so can you help me again?

Thanks for the first reply by the way. :)

I tried using asterisk with quotes but its evaluating it as an string character.

I also tried using an asterisk only but it says "The expression failed evaluation."

I would like to concatenate @FileDate and @FileName and a wildcard.

ex. @FileDate = 11091988_

    @FileName = _Kate

result: Kate_C_Green

I'm using a wildcard so it can return files that match both the @FileDate and @FileName regardless of what is in between. I'm trying to configure my for loop container.

Hi Jamie,

I already have a ForEach Loop container and inside is my file system task.

My file system task searches for a group of files using a particular date.

While my ForEach Loop filters it using their filenames.

On the editor, I tried adding the expression @FileDate + "*" + @FileName on the file textbox. But it returned an error saying file not found when in fact it is there.

I tried evaluating the expression on the property expressions editor but it says "The expression failed evaluation."

How can I filter the group of files uisng their filenames by concatenating variables and wildcards? The editor don't seem to accept it? Any ideas?

Hi Jamie

Need your help in this.

I have a FTP Task inside a for loop. For loop is for variaous company names. Depending on company name I need to FTP files to different FTP servers. How can I achieve this dynamically?

Thank s in advance

I'm dynamically building a filename with a date component in it using Expressions to open an Excel file via an OLEDB source connection.  All well and good.

Alas the workbook is also dated, e.g "Sep 07", and there doesn't seem to be a way of setting the "Name of the table or the view" attribute on the OLE DB Source via an expression.  I have the calculated value and stored it in a variable but can find no way to assign it via an expression to this attribute - am I missing something or is this an oversight by M$?

I am trying to insert the number of records in the file name of a text file that I have set to automatically populate with results from a query.  I have been stuck on this for a couple of weeks now.  Does anyone know the best way to do this?

Is there a way to conditionally load files using Foreach File Enumerator? For example, I have files with timestamp in the file names and I want to load only files before certain timstamp.

Jaime,

Thank you for the quick reply.. before I saw you note, I tried another way and it dose not work

I set the following expression for "FileSpec" property in Foreach Loop

@[User::CountFileName] < ("COUNT" + @[User::FileStopTimestamp]  + ".TXT")?  @[User::CountFileName]  : "wait.txt"

@[User::CountFileName]: the variable to hold file name in each loop

@[User::FileStopTimestamp]: the variable to hold the cut off timestamp

I thought the above expression would return a  file name if the file name is smaller than the cut off file name. While I evaluated the expression it did what I expected. But when I run it, it seems only get evaluated once and use that value for all the entire loop. Is that something by design? Can we dynamically reset "FileSpec" in a loop?

Back to your suggestion, do I need a script to check file name?

Jaime,

Thanks a lot! It works but I have to put a dummy job before my data flow job so that the precedence constraint can be set before the data flow job

Expression in the constraint.

@[User::CountFileName] < ("COUNT" + @[User::FileStopTimestamp]  + ".TXT")

i want to give complete filepath dynamic.

filename is fixed.

filepath value is present in user variable @[User::FileShareLoc] .

@[User::FileShareLoc]  + "\\ABC.csv"

its giving error

how to write expression.

Hi,

I'm trying to set the Destination Name of e File System Task using a variable, but I get the error "The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property. " I'm guessing it has to do with the scope of the variable but I have no idea how to fix it. Any ideas?

Hi Jamie - thanks very much for all the work you do on this blog; I've had to seriously ramp up my expertise on SSIS and this has been invaluable information.

One related topic I have run into, and I wonder if you have any insight: order of evaluation for related variables that are set by expressions. I had this intractable bug in a package that dropped data out to several files, where the file names contained a date stamp.

The original design was to make one variable containing a formatted datestamp as some long expression like

(DT_WSTR,4)YEAR(@[System::StartTime]) +

RIGHT("00" + (DT_WSTR,2) MONTH(@[System::StartTime]),2) +

RIGHT("00" + (DT_WSTR,2) DAY(@[System::StartTime]),2)  +

RIGHT("00" + (DT_WSTR,2) DATEPART("Hh",@[System::StartTime]) , 2)  +

RIGHT("00" + (DT_WSTR,2) DATEPART("mi",@[System::StartTime]) , 2)  

then, to avoid repeating that whole beast, the file names were set with separate variables like

@OutputFolder + @PropertiesFilePrefix + "_" + @datestamp   + ".txt"

I was noticing the job failing, and found that the datestamps were not being consistently set in the file names, so sometimes a file would get the right stamp, and sometimes not.

I had a hunch it has to do with the order of evaluation of the variables' expressions, so I eliminated the @datestamp variable and copied the whole expression into each file name variable. That seems to have fixed the issue. Have you run into anything like this?

Yup, definitely. The strange behavior was that in one execution of the package, the datestamp would make it, correctly, all the way into some output file names, but other file names would come out wrong. Strange.

I am new to SSIS and I have what I thought was a simple operation to accomplish. I need to capture the date when a package starts and use it later in a script task that resides in a foreach loop. The first task in the package is a script task and I am using the expression editor to assign a value to a package variable (used the above example -works well!). I am trying to access that package variable in the second script task but I get the "failed to lock variable" error.

I have seen everybody using the RIGHT function. Can the LEFT function be used?! Very silly to ask, but i tried using LEFT and received an error saying function not defined or something along the lines; using RIGHT worked just fine. What gives?!

Thanks Jamie!!!!!! I was about to pull out my hair cause I everything else looks good and i didn't suspect the LEFT function to be the issue. Hey you have an awesome blog going! Love it!

is there a list of functions that can be used in Expression out there somewhere?

This is the sql equivalent of what i am trying to do

DECLARE @STR VARCHAR(10)

SET @STR = '1234.CSV'

SELECT CHARINDEX('.', @STR), SUBSTRING(@STR, 1, charINDEX('.', @STR)-1)

This is the sql equivalent of what i am trying to do

DECLARE @STR VARCHAR(10)

SET @STR = '1234.CSV'

SELECT CHARINDEX('.', @STR), SUBSTRING(@STR, 1, charINDEX('.', @STR)-1)

Yay! It did! Thanx Jamie.

On a side note, while editing the expression for variables, I do not seem to find the Expression Editor or could I be any more dumber!! LoL

Thanks Jamie!

Hi this is a great but i have one problem am trying to solve my self for most of the file name you looking at name with dates in them. But with my problem i have the week number as part of the extension an example of this is

filename.A16 so the file name would look like that SampleFile.A18 where 18 is the week number.

Is it possible to create an expression to do this i tried but am not having much hope..

Hi Jamie,

First of all thanks for your blog... very useful!

Well, I'm looking for some example or tutorial of how to make an optimized package on SSIS to keep two tables (different servers) updated. Can you help me?

Many thanks so far!

-Leandro

This is a great thread but i need a bit of help.

I have a flat file which i get exported upto an FTP server daily with details of the days phone log files. They are pushed up at 10.00pm everynight so im writing an SSIS package to grab the file up there and import the data into the SQL database i have. then to delete the file up there..

The problem i have is that the flat file name is in the format Record.yyyymmddXXXXXXX where X is a random number.

Is there any way i can use a wildcard in the connectionstring to the file to open the file up there?

Regards

Sean

jamie..

Whats the best way to implement it?

Ive tried adding the multiflatfile but it doesn't recognise my file..

Cheers

Sean

Hi Jamie! It's your pal Emily :) Your post has really helped me out like always! I was stuck on this thing for 2 days, i should have looked you up earlier...

Jamie,

Is there any way to tell SSIS when to re-evaluate variable expressions? For example: I have a variable that defines a filename using a date and time expression (filename_yyyymmdd_hhmm). When I go back and try to do something with that file in a later step and the time has changed, according to SSIS the file does not exist. Very annoying!

Thanks,

Aaron

OK, so I have the correct path for a file, sourcePath. Now how do I use it in for instance a BulkInsert Task? When I put it in as the source connection, I get the error message that the connection <sourcePath> is invalid. I can't figure out how to configure a a file connection using a variable or expression. They want a SPECIFIC location (path)?

Thx,

Joe B

If I want to open a file where the name changes every 5 minutes but always contains a "ante", how would I do that?

Hi,

My FileName  is in format yyyymmdd.txt I want to retrive the File between specific Date.for example, I want to retrive the file  between the date  20080906 to 20080908. How Can I Do it. Please suggest me the expression.

Jamie,

Excellent site.  I got a much better handle on SSIS thru sites like yours.  Very helpfu.

I have a task to do that is very similar to what Kate wanted to achieve.  I need to automate a daily import process that takes a flat file with a file name that includes today's date and a time stamp and import the data into the database.  The file name looks like "Mainfile_20081101_012345.dat" so I tried to use either a script task to match and find the file or set the expression in the connection manager both with a "*" wildcard for the time stamp part but none of them worked.  I created a global variable "FileName" to do this so the update can be passed on to all tasks in the pkg.

To match the date part of the file name was simple but when I use the "*" wildcard, it always evaluated as "Mainfile_20081101_*.dat" and the pkg failed because the file didn't exist.  The only way I could get the package to work was to copy the exact file name and run with it.  It seems to me that wildcard isn't supported in SSIS.  Since this stopped me from being able to automate the process so I'd really like to find a solution to it.

Since I only need to process one to two files per day, it is not necessary for me to use For Loop to process all the file in the folder.  Do you have any workaround to this?  

Thanks much in advance.

Great blog!  I've got a series of files with the follwing naming format:

IDNumber_Q1.xml

IDNumber_Q2.xml

IDNumber_Q3.xml

...

IDNumber is the only variable part of the file naming structure. The remaining is constant. Using the for-each file enumerator loop, for the file name expression, I try using *_Q*.xml, but get a file not found error. I can use *_Q1.xml and it works fine for the Q1 files, but it won't process the second wildcard. I've even tried using the *_Q?.xml format and it gives the same error. Any ideas? I'd prefer not to create seperate loop processes for each Q* file as the format and contents are consistent and can be handled in the same loop process if the files can be identified.