Hi,
I've read the various posts and articles regarding this matter, but I seem to have problems getting to work:
In my control flow, I start by declaring a variable named "LastJobLedgerEntryID", to identify the records I need to add to the stage. From there I would like to use this variable in the source component in my dataflow, i.e.:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
But this fails I should note that the variable LastJobLedgerEntryID is stored as a int32, and with the default value of 0
Could someone please help me with this
Thanks in advance!
Passing variables to data flow component
Grandpa DBA
You could try doing this instead: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
Some people don't like it as it raises the risk of SQL Injection - personally I'm fine with it.
-Jamie
P.S. If you're posting to ask for help upon getting a failure, it really helps if you provide the error message.
Michael Lambrinov
Thanks alot, that helped!
Although, I think the (DT_STR) should be moved like this:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > (DT_STR)" + @[User::LastJobLedgerEntryID]
Anyway, thats the way it worked for me
April Reagan MSFT
Hi Jamie,
Okay, its my first post here, so I'll keep that in mind in the future.
Actually I have read that article, and that is exactly what I am trying to do:
The sql I write in the variable is:
"SELECT * FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
And the error showing in the variable proberties, value is:
"The expression for variable "FetchJobLedgerEntriesSQL" failed evaluation. There was an error in the expression."
Now, I said that the variable LastJobLedgerEntryID was stored as int32, however, if I change it to a string type, I get no errors in the variable proberties window. Now, if I then try to use this variable in a source component in my data flow, I get this error:
TITLE: Microsoft Visual Studio
------------------------------
Error at Job Ledger Entries [OLE DB Source [31]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Line 1: Incorrect syntax near '>'.".
The variable is initially set to 0, so I suppose it should still parse correctly, but perhaps I am missing something here
Dave Phillips
Huh. Are you sure Assuming the expression evaluation didn't fail then your SQL statement would look like this:
SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > (DT_STR) 12345
Try pasting that into a query window in SSMS and see how much data you get. [The answer is "None, it'll error"]
-Jamie
roniwu
Oh of course. Sorry, I should concentrate more.
Your expression needs to be:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + (DT_STR) @[User::LastJobLedgerEntryID]
Sorry!!
-Jamie