Using variables within a Data Flow ?
-
I think either I cannot see the wood for the trees, or I have not made the required switch to SSIS mindset from DTS. Here is the problem:
In SQL 2000 I had a DTS package that queried remote audit tables for any additions/changes and then applied those additions/changes to a local table. To facilitiate this I set up a local table called import_timestamps with just two columns - table_name and import_stamp. When I wished to update the local table I would grab the import_stamp for the relevant table and then assign it to a variable. I then queried the remote table for all audit records where the timestamp was greater than or equal to the stored timestamp value (the value in the variable corresponding to the last time I updated). This was really easy to do in DTS. SSIS appears to be a different story - yes I can query my import-timestamps table and assign the timestamp to a package variable, but how on earth do I use that variable to grab the specific audit records ? I thought that inserting a data flow component in the control flow and then setting the source to the remote table and the destination to the local table would do it. Of course the source would have to be an SQL statement that referenced the timestamp variable, but to my dismay there appears to be no way to do this in the data flow. How on earth do I get around this apparently simple task in SSIS? Any help or pointers to web-sites etc will be appreciated.
In closing, I am finding (as many are I am sure), that though SQL2005 has much to offer, understanding the functions of SSIS and using them requires a radical mind shift. This appears to be Microsoft's strategy of late - take a good product (SQL2000 DTS), and then instead of improving on it using the same basic foundation, throw it out and produce something totally different. Even the on-line help leaves much to be desired. Enough bitching and moaning. Looking forward to any forthcoming advice or help.
Best Regards
Terry -
To use the variable in an excute sql task set the sql statement to something like the following
SELECT * FROM HumanResources.Department WHERE DepartmentID = ?
set bypass prepare to true
and then in the parameter mapping option set the variable name to the name of your parameter and then set the direction as input and the appropriate data type. The parameter name should then be set to 0. If i had two pararmeters the second one would be called 1 and so on.
It is very different to DTS and I'm glad someone else finds BOL not much use on the subject I ended up buying a book at which point it does make sense honest.
hth
David
-
Thanks for your swift response David but perhaps I should add some supplementary information. In the Control flow all is well up to and including the variable assignment (I have run this step in breakpoint mode to check that the variable is being updated). Now what I need to do is to transfer the audit records that match the variable criteria into a 'holding' table, counting the rows along the way.
As I said, I thought of inserting a data flow component in the control flow and then in the dataflow setting the oledb source to a statement like " Select * from <table> where audit_timestamp >= ?" I then foolishly thought I could just map the package variable into the input parameter for the sql statement, set an oledb destination as the 'holding table' and all would be well. Should I not be trying this in a data flow ?
BTW I bought a book too - obviously the wrong one - co-authored by ten individuals that I believe lost the plot in what they were trying to do !
Best Regards
Terry -
I've taken the approach in my packages that getting the value for and assigning it to the variable is done in one data flow and then I would either create an execute sql task in the control flow that executes once its complete or create a sewcond data flow which does the next bit depending on what I am doing.
In your case I would probably do the first bit in one data flow i.e. getting the value for the variable and then in a second data flow I would do what you are suggesting i.e. OLE DB Source and then flow to a row count transform then into my ole db destination.
Are you just having trouble mapping the variable?
BTW I think i know what book you mean there's some mistakes in Chapter 6 in the advanced transforms bit.
David
-
Thanks again David. My problem is mapping the variable, but again let me try and clarify. In DTS 2000 I would create a variable and assign a value. In a simple data pump task I could create a source connection with an sql statement such as 'Select * from <table> where <column> = @MyVar' I could then establish a transformation to a destination table - no problems.
Now imagine with SQLS2005 I have a package variable set (@MyVar) already, how do I perform something similar to DTS2000 ? If I set up a data flow container and then an oledb source as a direct SQL statement and an oledb destination in the data flow screen, I can place a row count component in between and link the source and destination to transfer rows from source to destination. BUT, how can I set up the source SQL statement to use my variable as a filter on the rows returned or transferred ? Whereas with an SQL Task component in the control flow you have options for Parameter Mapping and Result Sets, in the dataflow source component there are no such options.
Has this explained it better ? I have come up with a couple of solutions that will do the job but they are not as quick and easy as something like the DTS solution would be.
Best Regards
Terry -
There is one thing you may need to check first and thats the scope of your variable make sure its at a level where it can be seen in the ole db source the easiest thing to so is to set it at the level of the package. Once its been created at one level you can't change the scope. This was my probelm when I initially tried to do this a few weeks ago. I set the variable at the level of the transfom in which i set it and couldn't nsee it in the ole db source.
If its set at the correct level then all you need to do in the ole db source is set the data access mode to sql command and then eneter an sql statement e.g.
SELECT * FROM MyTable WHERE Something = ?
the parameters button will un grey click on it next to parameter 0 click on the variables drop down if your variable is the correct scope it will be in the list and you can map it if its not in here it has defined at the wrong scope. So you just need to check at which scope it is defined and recreate it at the corect scpe nad the you will be able to map it.
David
-
Teegee,
Fear not. This is very easy in SSIS once you know how. All explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
David and Jamie,
Many thanks for your help on this matter. During the last two days I have made progress eliminating this and that using some of your expertise as a guide. Todays plan was to use an SQL variable (as your excellent blog points out Jamie). I now realise that in my explanations of the problem I left out one important fact - I am connecting to AN ORACLE SOURCE and an SQL 2005 destination. For some reason the parameterised query will not work with the Oracle oledb provider:
"Provider cannot derive parameter information and SetParameterInfo has not been called (Microsoft OLEDB provider for Oracle)"
I mention this in case others using Oracle are experiencing similar issues.
Now all I need to fix is the infamous Oracle "ORA-01861 Literal does not match format string" ... Oh yes it does. Oh no it doesn't. Oh yes it does. Oh no it . . . .
Best Regards
Terry -
Terry,
That's why I like the expression approach. It means those issues won't appear!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
TeeGee -
Did you find another solution to this other than "SQL command from variable?" I am having the same issue - using paramerized queries in Data Flow task against Oracle data source.
Any advice would be appreciated.
Mitch
-
Mitch,
No I didn't, but the variable method works fine. I suppose at first I was loathe to have heaps of variables hanging around if there was a slicker way to do things, but now I getting used to the variable and expression features of SSIS. I just wish it wasn't so damn unintuitive at times !
Best Regards
Terry -
Terry,
Do you have any thoughts as to how it could be "more" intuitive?
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Well Jamie,
It's hard to be clear and precise about this because we all learn differently.
As I am approaching my twilight years (!) I am not the fastest kid off the blocks but I do have enthusiasm and drive. What I need are concepts - yes, but most importantly examples on how to perform various tasks. Your articles (and this is not just blowin' smoke up the proverbial), have been invaluable to me, as have other articles I have found on the web. I don't think I am alone when I say that BOL are not what they should be, and my perception is that though the IDE has been made to look simple enough, the supporting material is somewhat lacking and the target audience appears to be more advanced programmers. Only yesterday it just dawned on me how to use the built in breakpoints properly. I didn't find the explanation for that intuitive, it was a case of try it and see that finally dropped the penny. A step by step guide to using this would have saved me so much time.
In fairness I suppose I can jump in at the deep end too soon sometimes, and then find myself scouring the web and all the books I can find to solve a problem/challenge. Here's an example of what I find really useful and supportive: SQLServer Transact SQL module of BOL. It's all there - concepts, explanations, followed by lots of practical examples. Now in SQL2000 it was dead easy to find the topic. What on earth happened in SQL2005 ? Yes, the same quality of material is there at the end, but finding it - well !
Take this example:
Select properties of an SQL Task. Hmm ... ForceExectionValue, wonder what that is about - how could I use that? Click it and press F1. Oh, help on Task Class - OK, search for ForceExecutionValue. Good 48 topics to look at ! With not one of them I might add in plain simple language. . .
Enough, I'm showing my age, sounding too negative, and going off track. Overall, I am enjoying the challenge of working with SSIS and am starting to turn out some good stuff. The satisfaction out of achieving something is still better than drugs ! Keep up the good work. I'll be back in the UK for a spell next month so turn on some sun will you. It gets down to 21 at night here in tropical Cairns at the moment - real brass monkey stuff !!
Best Regards
Terry -
Interesting stuff. It does infuriate me when I click on a help button and it takes me to something only barely related to what I really wanted. Will that ever change? Somehow I doubt it.
One good thing about the changes to BOL is the feedback link. I've used it many times and invariably the changes I have requested have gone into the next release.
I agree that finding stuff is difficult too!!! Most definately. I usually resort to google.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Mitch,
What is wrong with the "SQL Command from Variable" approach?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply