Extracting Data from a rowset
-
I am using an Execute SQL task to get a single row of 7 columns of data back. I need to put the data into 7 variables. The only thing I can get to work is a rowset object but now how do I get the data OUT of the rowset INTO the variables and WHY can't I just put the variable list into the ResultSet pane?
Thaks,
Joe B
-
Joe
If you are only returning a single row, what you need to do is set the Execute SQL Task ResultSet property to be Single row.
Then, in the Result Set page, map each column in the result set by name to a user variable. You can set this either via the actual name of the column being returned or through ordinal position.
so, a simple query of "select 1 as first_column, 2 as second_column"
this would have two mappings in the Result Set page like the following:
Result Name\Variable Name
first_column\uservar1
second_column\uservar2
hth
-
Very simple package:
A ForEach Loop container with an Execute SQL task in it. Variable mapping in the ForEach is to User::FileName. The collection Folder points to a folder on my local disk which has a file "PROD_INPUT_20080811.CSV" I'm retrieving file name and extension. Files is set to *.*
HOW do I see the SQL and variables being executed??????
I set the Execute SQL task as a Direct Input with ? pointing to the variable Prefix
which is setup as Evaluate as Expression = True with the expression:
"'" + (SUBSTRING(@[User::FileName],1,10)) +"%'
I set the SQL as:
Select C1,C2,C3,C4,C5,C6,C7 from TBL1 where C1 LIKE ?
This FAILS with no data returned.
If I instead setup Execute SQL as a Variable with the variable FSQL
setup as Evaluate as Expression = True with the expression:
"Select C1,C2,C3,C4,C5,C6,C7 from TBL1 where C1 LIKE '" + (SUBSTRING(@[User::FileName],1,10)) +"%'
The package will execute but I get NO VALUES in the variables.
WHAT am I doing wrong???????????
I thought the variables pane should show me what the last package execution values were but I see nothing. How do I look at what's coming INTO the Execute SQL task BEFORE it executes?
Thanks,
Joe B
-
This makes absolutely NO SENSE!
The FileName variable NEVER gets populated from the ForEach loop container. The scope IS the ForEach container. WHY?
I have hard coded the SQL into the Execute SQL task and NONE of the variables gets a value. If I run the SQL from the SSMS I get what I expect. It can't be a type issue everything is string in the package and the database values are varchar(50).
Could someone PLEASE take a shot at explaining this. This is making me NUTS!!!!!
THX
Joe B
-
you could change your sql code to the following:
Select C1,C2,C3,C4,C5,C6,C7 from TBL1 where substring(C1, 1, 10) = ?
and change your variable that is evaluated as an expression to just SUBSTRING(@[User::FileName],1,10)
you can use breakpoints and look at the local variables (or set up a variable to be watched) to see how the variables are being populated during your loop.
Tom
Life: it twists and turns like a twisty turny thing
-
Tried that. I even went back to the original package which was working and this package was a copy of. The ForEach loop is NOT returning a valid value although nothing has moved or changed in the folder I point to. This is on my laptop and ONLY I work on it. More Microsoft flakiness. There is absolutely NO reason that I can see as to why this is happening.
Thanks anyway,
Joe B
-
Before we put on the MS bashing hat, I really suspect it is a user error.
Is the enumerator for each file? YES
Is the folder a valid path? YES
Is there prehaps an expression which is overriding it at runtime? NO
Again, THE ORIGINAL PACKAGE WORKED. My development method from 25 years plus of developing and working for the government is:
You get something to work, document it, make a copy, continue development on the copy. I only mention the government because if you've ever worked for the military arm they get VERY detailed and every step NEEDS to have verifiable results. 10 years of working for them pounded the habit into me.
Anyway, I took a copy of the original package which worked thru 3 files correctly and started to add more code. After 2 days of working with the copy I kept running up against the variable FileName which is the only variable in the ForEach loop not getting a value. Again, this is a copy of what was working. So I decide to start over, made another copy, after cleaning up the first copy. I run it and get a file not found error. Nothing has been moved from the original development setup which worked. I scratched my head and loaded the original. Now it is showing the same behaviour. NOTHING and I mean nothing in the development setup changed other than the addition of copies of the package.
So where is the hole in the process? This is the ONLY project I've been working on. The only other thing I've been doing is reading and responding to emails. I have other machines to do other things on. I am a contractor and when I work on a project I dedicate one machine to the contract. When I'm done, I archive it off to CD and reformat the external drive where I have been working from.
Thanks for the reply but I'm still in the same situation.
Joe B
-
I smell a rat. I really do.
It's great that you been doing things for government and military and all that. Fantastic you felt the need to add it.
Now, the fact that it failed after you "cleaned it up a bit" says something.
What is the actual error? Does it just not loop? What did the watch on the variable say when you ran it? You did add debug to the variable right? Monitor it's value at run time.
To copy a package will, as I am sure you know, have no effect. It's just a file copy. Sometimes we all make silly mistakes and delete / change something. I do...
Hold up...
Just had a read through the post again.
Your original way was almost correct except that you did not have a % in your statement. But later you insist that the file name variable is blank. What makes you say this? The fact that SQL returns nothing or did you add a watch? (Remember, you cannot look at the value of the variable in the variable pane while running. this is not updated)
So, if you fist disable the SQL and let's ensure you have a valid file name in the variable. Either add a watch (Debug button > Watch windows.) or use a script task to msgbox it.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
So do I, it's just being rat-like and is buried somewhere.....
I've decide to scrap everything and start over. I do have all my notes and settings. Lets see where that gets me. I'm just under a BIG time crunch and already reported that the core worked. That'll teach me. Ahh for the days of assemblers and switch programming.....
Thanks again,
Joe B
-
Been there, done that, bought the T-Shirt 🙂
I do suspect though that it would be quicker to disable everything and slowly work forward until you find the problem. It's a for loop, not that many things that you can break 😉 [Except that one elusive thing...]
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thanks Crispin. But so far redoing it has brought back what I should have been seeing. Still some work to do but bug (and rat) free so far.
Joe B
-
Crispin,
Got everything working (almost, still have some dynamic SQL to figure out) but it took another total rebuild of everything. Seems it's not a good thing to have 2 instances of BIDS running at the same time to cut and paste scripts. When I only had one instance open and used a text editor to move code that seemed to work.
Anyway, thanks for the calming intervention.
Joe B
-
Glad it's working...
Copying script tasks has it's issues. There is a nasty one where copy + paste and run, it runs code from the first one. I cannot remember the exact sequence to recreate it but when I did have it a few months back, it blew my mind. had me stumped for days.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Would THAT be a valid example of when to put on the MS bashing hat?:D:D
I won't be opening multiple BIDS instances again, that's for sure. May take a little longer to move stuff but definitely worth it based on this debacle. A side note; I'm looking for a way to generate dynamic SQL for an insert statement by reading from the sysobjects, syscolumns. Do you know of any code around that does that or something similar?
Thanks,
Joe B
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply