April 28, 2015 at 9:20 am
SQL 2008
Absolute peach of a question. I have a number of OLEDB sources in packages that look at SQL Server and the SQL is stored insaide a variable string.
So open up the OLE DB Source and in the editor you will see SQL Command from Variable is selected. The correct variable is selected and the variable value is shown as
SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= '[DateChanged]'
I cant see an expression on the OLE DB Source
I cant see an expression on the Data Flow Task
There is no variable called DateChanged
There is no Parameter passed to the package called DateChanged
The packages are executed by a parent (or control) package. But the parent packjage does not pass this parameter \ variable.
Any suggestions
Cheers
E
April 28, 2015 at 9:29 am
Only other thing I can think of is a configuration file maybe?
April 28, 2015 at 9:31 am
Is the value of the variable itself an expression?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 28, 2015 at 9:46 am
Oh and another thing. When opened in BIDS the OLE DB Source will error as it compares a date to the string '[CHANGEDATE]'
But run by the parent package and there are no issues
Cheers
E
April 28, 2015 at 9:54 am
Phil,
this probably is harder to describe by writing than showing. The SQL for the OLE DB Source is a variable (not using expressions just hard coded) AS
SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= '[DateChanged]'
Now there is a task prior to this that updates the value of that variable just prior to execution. SO when the package starts I guess it validates against the value in the variable at start up, then when it starts to execute it executes the code that we retrieve from a table that contains SQL statements. I have not checked what the sql is changed to at run time by setting break point but have checked and it should be set to the same value it started as! (Please dont ask why - I am not the author of the work)
I have checked the table where we store the sql statements and the variable should be updated to the same value as it has before the package is run (Will check this ).
Oddly run through BIDS this will not work ever, even if you disable the step that updates the variable - it just fails. Run through a SQL Agent job (not run as 32 bit mode) and the package runs, loads data and completes
E
April 28, 2015 at 10:22 am
Ran the package with a breakpoint before the dataflow task. Now the SQL it picks up from our SQL table is "SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= '[DateChanged]'"
but when we look in the local variables we can see that the variable holds the string
SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= 01\05\2015
So from retrieving the string from the database and placing it into the variable the value of the characters '[DateChanged]' have been changed to 01\05\2015
?????
Rather Puzzled
April 28, 2015 at 11:48 am
Do you see a variable that ends up holding the date that gets added in?
April 28, 2015 at 12:46 pm
Nope,
STEP 1
In the Variables for the package there is a variable that holds the string SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= '[DateChanged]'
STEP 2
Then there is an execute SQL task that places a varchar(4000) column from a SQL table that holds all of the SQL statements to be used. The Execute SQL Statement puts the result set into the same variable.
STEP 3
Then there is a data flow task, the first part of this is an ole db source that executes SQL from command variable
So when in debug mode
AT STEP 1 before anything is executed you can see the default value for the variable and it is as expected i.e SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= '[DateChanged]'
AFTER STEP 2 The contents of the variable change to become SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= 01/03/2015
STEP 3 is where the fun begins. When run as SQL Agent job all is well. When run in bids if you open the OLE DB source then it complains that a date is being compared to a string. Now that only seems fair as anything in single quotes is a string so that seems fair does!
So how come it works in SQL Agent but not executed in BIDS on a 32 bit operating system?
Where the hell or where ever does the 012/03/2015 come from??
There is no variable within any scope in the package!
This is not being passed as a parameter from a parent package.
This is not in any execute DTUTIL command line I can see anywhere
I can not see it in package configuration
I did wonder if it had been declared as a variable at some point in an execute SQL task on the SQL Server and if the connection was retained etc.....
I can not see it as an environment variable.
This is why I think its an amazing puzzle.
E
April 28, 2015 at 3:12 pm
Can you post the XML of the execute SQL task with anything sensitive omitted/renamed?
May 1, 2015 at 5:23 am
I started looking through the packages again and noticed a difference with this package.
While looking back for this package I checked a few others. This one is different
In all the other packages:
There is a variable that holds the SQL to be used : SELECT ID ,Description FROM MyTable
Then there is an Execute SQL Task to retrieve the SQL from a table of SQL statements. This then repopulates the variable holding the SQL with
SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= '[DateChanged]’
Then there is a data flow that has an OLE DB source that uses SQL from variable to extract data.
Now the problem in my bad package is the variable holding the SQL includes the where clause at the end. The packages that work do don’t include the where clause.
This means I have resolved the issue BUT
For my own sanity I need to understand where and what [DateChanged] is.
The closest I can get is to put a Pre Execute constraint on the data flow and I can see that when the variables value is retrieved from the table holding the SQL it magically transforms to a date value.
SO in my SQL table the varchar field contains ‘SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= ‘'[DateChanged]’’’
When this is put into a result set from the Execute SQL task it becomes
SELECT ID ,Description FROM MyTable WHERE LastUpdatedDate >= 01/03/2015
Any suggestions?
May 1, 2015 at 6:25 am
Found the answer. The SQL statement was retrieved via a stored proc that replaced the string '[DATECHNAGED]' with last months date.
Bangs head on Wall
Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply