I've got a situation that I'm SURE must be my own fault but I can't figure it out. An expression that should evaluate to yesterday's date evaluates 12 times correctly and two times incorrectly within seconds.
The general outline is that I've got a set of 7 files that I need to pull into sql server everyday. The files have names like "extract_account_1_090825.txt" and "extract_name_3_090825.txt" where the "090825" part is yesterday's date in YYMMDD format. The rest of the file name is static. I pull them in with a Bulk Insert Task. Each file has its own Flat File connection manager with a property expression set for the ConnectionString (e.g. for the Account connection manager
)
@User::OdbcExtractDateString is also set with an expression --
which makes a large integer from yesterday's date in the form YYYYMMDD, converts that to a string and then takes the rightmost six characters. The other variables are constants really but I've made them variables for future flexibility. All of the other flat file connection managers have similarly configured connectionString properties.
A complication is that the files are large and might not be completely delivered to the source directory at the time I have the package scheduled to start. To deal with that, at the beginning of the package, I've created a script task that spins through the connection manager collection and for each flat file manager that it finds, it checks to see if the file exists and if it can be opened (if it can't be opened, it is probably still being delivered to the directory). If the files can't be found or opened the task goes to sleep for 5 minutes and then checks again. On the other hand, if all files can be found and opened, then it continues to a sequence container that contains tasks that truncate the destination tables and then bulk insert the data from the files.
So, here's the problem. The past two days, for two of the files, the expression has evaluated the date correctly during the script task and incorrectly during the bulk insert task. The other five file names (connectionStrings)are correctly evaluated in both cases each time. That is, for today, the Account connection manager connectionString evaluated as 'extract_account_1_090826.txt' during the script task and evaluated as 'extract_account_1_090823.txt' during the bulk insert task and consequently the wrong file got loaded. The Loan connection manager worked the same way. I know how it evaluated because I log the open attempts during the script task and the sql statement during the bulk insert task.
It worked incorrect yesterday and today but on Monday and Tuesday it worked correctly. It broke on the same two files each day and substituted the same value "090823" each time. And, this only happens during the scheduled task at 6:00 am. When I run the package myself from BIDs it works correctly every time. When I run the same exact package in a one time schedule it works correctly every time.
One other clue. The @User::OdbcExtractDateString is exposed in the package configuration file and the default value is "090823", that is, the value that got substituted in the incorrect file names.
Has anyone else ever heard of an expression that evaluates sometimes and uses the default value at other times?
And, of course, if that is the problem, I could work around it by removing the default value from the configuration file. But why did it happen at all and, unless I understand what I've done wrong, when will I do it wrong again?
Any ideas or questions?
Long post... hopefully clear... Sorry and TIA.
Don
The general outline is that I've got a set of 7 files that I need to pull into sql server everyday. The files have names like "extract_account_1_090825.txt" and "extract_name_3_090825.txt" where the "090825" part is yesterday's date in YYMMDD format. The rest of the file name is static. I pull them in with a Bulk Insert Task. Each file has its own Flat File connection manager with a property expression set for the ConnectionString (e.g. for the Account connection manager
Code:
@[User::OdbcExtractPath] + @[User::AccountFileNameStub] + @[User::OdbcExtractDateString] + @[User::AccountFileNameExtension]@User::OdbcExtractDateString is also set with an expression --
Code:
right((DT_STR,8,1252)(year(dateadd("day",-1,getdate())) * 10000 + month(dateadd("day",-1,getdate())) * 100 + day(dateadd("day",-1,getdate()))),6)A complication is that the files are large and might not be completely delivered to the source directory at the time I have the package scheduled to start. To deal with that, at the beginning of the package, I've created a script task that spins through the connection manager collection and for each flat file manager that it finds, it checks to see if the file exists and if it can be opened (if it can't be opened, it is probably still being delivered to the directory). If the files can't be found or opened the task goes to sleep for 5 minutes and then checks again. On the other hand, if all files can be found and opened, then it continues to a sequence container that contains tasks that truncate the destination tables and then bulk insert the data from the files.
So, here's the problem. The past two days, for two of the files, the expression has evaluated the date correctly during the script task and incorrectly during the bulk insert task. The other five file names (connectionStrings)are correctly evaluated in both cases each time. That is, for today, the Account connection manager connectionString evaluated as 'extract_account_1_090826.txt' during the script task and evaluated as 'extract_account_1_090823.txt' during the bulk insert task and consequently the wrong file got loaded. The Loan connection manager worked the same way. I know how it evaluated because I log the open attempts during the script task and the sql statement during the bulk insert task.
It worked incorrect yesterday and today but on Monday and Tuesday it worked correctly. It broke on the same two files each day and substituted the same value "090823" each time. And, this only happens during the scheduled task at 6:00 am. When I run the package myself from BIDs it works correctly every time. When I run the same exact package in a one time schedule it works correctly every time.
One other clue. The @User::OdbcExtractDateString is exposed in the package configuration file and the default value is "090823", that is, the value that got substituted in the incorrect file names.
Has anyone else ever heard of an expression that evaluates sometimes and uses the default value at other times?
And, of course, if that is the problem, I could work around it by removing the default value from the configuration file. But why did it happen at all and, unless I understand what I've done wrong, when will I do it wrong again?
Any ideas or questions?
Long post... hopefully clear... Sorry and TIA.
Don