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
Dave Gol
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
PeterG_75
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
Therese P
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.
alex04
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
boexi
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