August 1, 2011 at 9:05 am
I am testing a package in BIDS. I have 5 Execute SQL Tasks that have the SQLSourceType set to Variable. The variables for these tasks are all defined and scoped at the package level, and they are all String data types. They are all very simple SQL Statements where I simply copy a file to a date stamped copy of the file. For example:
"SELECT * INTO dbo.dimHouseBackup" + @[User::DateStamp] + " FROM dbo.dimCustomer"
The @[User::DateStamp] variable is also a string data type. When evaluated for today this turns into:
SELECT * INTO dbo.dimHouseBackup20110801 FROM dbo.dimCustomer.
Invariably when I run these tasks one of them will fail. It is almost never the same task two times in a row. The error I get reads something like this:
"There is already an object named 'dimHouseBackup20110718' in the database."
Yes, there is already an object of that name in the database. But that is not the object I am trying to create, the date stamp causing the error is typically a week or 2 old, in the case above it is 2 weeks old.
Then after the task has failed, and I execute it again, it works because it uses the correct date stamp the second time.
Whenever I look at the variable values, the are ALWAYS evaluating to the correct date. I have DelayValidation set to True and BypassPrepare set to True.
Can anyone give me a clue as to why the variable would evaluate to a statement containing a date stamp a week or two old when the task is executed?
August 2, 2011 at 1:30 am
August 2, 2011 at 4:08 am
Do you run the entire package, or just a single task by right clicking on it and selecting "Execute Task"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 7:04 am
How is the current date is populated to the variable "User::DateStamp"?
It is populated by this formula
(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT( "0" + (DT_STR, 2, 1252) MONTH( GETDATE() ) , 2 ) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2 )
This ALWAYS evaluates to the correct date. I use this in several other packages as well without incident. Additionally the norm is that 4 out of the 5 Execute SQL tasks that use this date stamp will succeed with a properly named backup.
Do you run the entire package, or just a single task by right clicking on it and selecting "Execute Task"?
First, I run the entire package. Then after one of the tasks fail and the other tasks in the package are complete, I go back and execute the task that failed with right click "Execute Task" and it always works.
August 2, 2011 at 11:52 am
Strange.... I tried re creating the scenario... and I dont face the issue... Steps followed
Created variable User::Query set to evaluate as expression
Expression:"select * into test"+ @[User::Stamp] +" from sysobjects"
Created variable User::Query set to evaluate as expression
Expression: (DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT( "0" + (DT_STR, 2, 1252) MONTH( GETDATE() ) , 2 ) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2 )
Execute SQL Task Settings: Source type: Variable. Variable:User::Query
delay validation set to false and bypass prepare set to true.
Ran it a couple of times by changing the system date and it ran without any issues....
August 2, 2011 at 12:16 pm
Strange indeed. I have been fiddling with this on and off for weeks. I have spent hours on Google searching for a clue. So far I have not been able to find any reason why this behavior would occur. Every variable statement always evaluates to the value I expect, then one task randomly fails.:angry:
August 2, 2011 at 2:20 pm
Daniel Bowlin (8/2/2011)
Strange indeed. I have been fiddling with this on and off for weeks. I have spent hours on Google searching for a clue. So far I have not been able to find any reason why this behavior would occur. Every variable statement always evaluates to the value I expect, then one task randomly fails.:angry:
Some wild idea:
are the task executed in parallel?
Maybe one of the variables is "locked" by a task, so that another one fails.
Or something like that. I'm just guessing here... 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 2:48 pm
I am running them in parallel. Interesting idea of a locked variable. I really hope that is not the problem because that has some pretty frightening implications in terms of package design.
I will try to run them serially and see what happens.
August 2, 2011 at 8:03 pm
@Koen- I believe running execute SQL tasks in parallel should not cause any problem as the values for the variables are evaluated before the tasks are executed[Please correct me if im wrong]. Ideally they should be evaluated for every run.
@david-2: to try an alternate approach, can you try assigning the the value to the variable using a task[ExecuteSQL task or a script task] and see if this occurs
August 3, 2011 at 7:00 am
Although I hesitate to accept the locked variable concept, I added precedence constraints between all the tasks and it ran 3 times without fail. Hmmmm. I think I will have to investigate some of the issues with parallelism to see if that could be my issue. Odd thing is, after running serially 3 times I broke the precedence constraints and ran it again in parallel and it succeeded. More frustrating than anything is the intermittent nature of this problem.
August 3, 2011 at 9:20 am
Clearly this is some kind of parallelism issue. Changing back to parallel began to have inconsistent results again.
I really want this package to run these tasks in parallel to save time. So, rather than build my dynamic SQL with SSIS variables, I built stored procedures with dynamic SQL and simply called the stored procedures from my Execute SQL task and everything works fine. Problem solved.
August 4, 2011 at 12:09 am
Daniel Bowlin (8/3/2011)
Clearly this is some kind of parallelism issue. Changing back to parallel began to have inconsistent results again.I really want this package to run these tasks in parallel to save time. So, rather than build my dynamic SQL with SSIS variables, I built stored procedures with dynamic SQL and simply called the stored procedures from my Execute SQL task and everything works fine. Problem solved.
Glad that you finally got a working solution.
Really strange parallellism issue.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 4, 2011 at 8:20 am
I tried 5 sql tasks in a row and had no problem.
Not saying that, this is the problem, because the system date should be the same on all servers you have, but using different connections could be a possible factor.
Are you using different connections for each SQL Tasks?
I don't normally work with packages.
Would the scope of the variables matter whether the expressions are run on the connection the control flow has or the connection specific to the SQL Task?
Dan this was good practice. 😎
August 4, 2011 at 11:49 am
1 package,
1 datasource
1 database
5 different source tables
5 different destination tables (dynamically created)
6 variables scoped at the package level, 1 to return the date integer, 5 to create the dynamic sql statement with the destination table name with the date integer appended.
It works fine serially. Each task when run individually runs correctly. Usually 4 of the 5 tasks when run in parallel will run properly and one (a different one every time) will fail. The failure will indicate that the the dynamically built destination already exists, but the kicker is that the dynamic destination it is using is a week or two old like there was something in a cache somewhere.
In any case my solution, there is always more than one way to solve a problem, of putting the dynamic sql into a stored procedure and calling the stored procedures in parallel works just fine.
August 5, 2011 at 10:24 am
Can I hazard a guess?
You're using SSIS 2005, and the DateStamp string variable is built using an expression off of other variables...
If that's the case - yep, it's what I'd consider a bug. There is no fix, only workarounds.
First option - reduce the "depth" of your expressions. Instead of DateStamp referring to a variable that constructs a date from another variable, put all the logic into DateStamp's expression directly. Yes, it makes DateStamp's expression impossibly complex. But it works.
Second option - don't use expressions, use scripts. Advice I hate, but it also works. Use a Script Task to "manually" evaluate what you want in DateStamp using C# or VB.Net.
Third option - upgrade to SSIS 2008. This seems to have eliminated the issue for me, but isn't always practical to do.
The cause, as far as I can see, is a flaw in the evaluation engine. Under some "stressful" circumstances (deep evals for me - parallelism for you?) it simply doesn't finish evaluating expressions. The value you end up with will instead be sourced from the "value" property of a variable set at design time. When I was dealing with this, I'd set all my variables' "value" properties to known-bad values and used a Script Task to test for those values, throwing exceptions if I detected them.
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply