November 21, 2011 at 8:57 am
You don't need a separate variable for each field in the returned resultset. It can all be contained with the same object variable, and then used in the variables mapping tab of the of the Foreach Loop editor.
Use the index (0 for the first field etc.) and assign those fields from the resultset to other variables...if you require to use them later.
November 21, 2011 at 9:04 am
Martin Schoombee (11/21/2011)
You don't need a separate variable for each field in the returned resultset. It can all be contained with the same object variable, and then used in the variables mapping tab of the of the Foreach Loop editor.Use the index (0 for the first field etc.) and assign those fields from the resultset to other variables...if you require to use them later.
Im not quite getting that which is me being stupid :doze:
It can all be contained with the same object variable (How would this work?)
and then used in the variables mapping tab of the of the Foreach Loop editor.
Again Im struggling to understand this....at all.
November 21, 2011 at 9:16 am
That's ok...it's a little confusing at first, so let me explain in detail.
Your SQL query (in the Execute SQL Task) returns an ADO Recordset object, which can contain rows and columns...basically a dataset.
You then assign this recordset to an object variable, which assumes the properties of the recordset (rows and columns of data)...this is done by using the Result Set section of the Execute SQL Task, and specifying the Result Name = 0 (as in my screenshot). The 0 (zero) means the whole recordset will be assigned to the object variable, as opposed to only certain fields within the recordset.
By using the same object variable (which now contains the data from the original query...rows and fields/columns) as input into the Foreach Loop Container, you will have access to all the fields that were returned by your original query. The catch is that you have to refer to them in ordinal position (i.e. 0,1,2 etc.).
So by assigning the field values from your recordset object (using the "Variable Mappings" section of the Foreach Loop Container properties), you now have the field values during each iteration in local variables.
You will have to play around with this for it to make sense. Create a simple package with your source query and Foreach Loop Container, and do something simple like showing a messagebox (script component) with the values during each iteration.
Hope this makes a bit more sense...give it a try, and post your test package if you have more questions or issues.
November 21, 2011 at 9:19 am
Right....
Im going in!!!
Ill work through what you have just sent me and let you know.
Thanks loads for this BTW!
November 21, 2011 at 9:54 am
Well Its going a lot better so far but Ive got confused at the variable mapping section.
Ive updated my blog with the story so far on this one with all my screenshots
I dont unsertand the field values bit. Ive created a new object variable called CreateVariable of 0 so that contains my 3 columns.
Ive gone to variable mappings in the fioreachLoop but I dont know how to assign these field values.
But I think im understanding it to that point.
Also the other thing I have been thinking. I have got 10 SPs that I want to put into the foreach loop. currently they look Like EXEC [Fact].usp_SA_TBLPUPIL_ATTEND_b @AC_YR
Will I need to change all thse to EXEC [Fact].usp_SA_TBLPUPIL_ATTEND_b @AC_YR ? Ive forgotten how this works
November 21, 2011 at 9:57 am
Could you please post your package...I cannot get to your blog from here.
November 21, 2011 at 10:00 am
I dont know how to do that unfortunately. Also if Im thinking correctly the package is really big. Ive got lots of stuff in there so Id have to create a brand new project to work from
November 21, 2011 at 10:03 am
I know it is extra work, but in order to see where you are going wrong it is better to look at the actual package.
If possible, please create a small test package for this purpose only...it will also benefit you to understand the concepts by playing around with it and testing with different options.
November 21, 2011 at 10:07 am
Im just trying to copy and paste the bits out from one to another, Its gone a bit wrong so Ill need to do a bit of fixing
Ill have a look at how to sent the file? to you tomorrow although at the moment I dont know how that works but Im sre I can figure it out.
Thanks again loads
November 21, 2011 at 10:09 am
Debbie Edwards (11/21/2011)
Im just trying to copy and paste the bits out from one to another, Its gone a bit wrong so Ill need to do a bit of fixingIll have a look at how to sent the file? to you tomorrow although at the moment I dont know how that works but Im sre I can figure it out.
Thanks again loads
Zip and attach...bottom section of the screen ("Post Options") if you add a reply.
November 22, 2011 at 2:18 am
I think I have done it (Hopefully)
I just ran the test and obviously it failed.
Im getting errors such as Error: The type of the value being assigned to variable "User::AC_START" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Your right though having a little test package is much better!
November 22, 2011 at 6:42 am
tried absoloutely tonens of things now including only using one parameter and Unfortunately Ive not gotten anywhere.
Really dissapointing but it looks like Im going to have to come up with another way of doing it because I only have the end of the day to get it implemented.
🙁
November 22, 2011 at 7:53 am
Debbie Edwards (11/22/2011)
I think I have done it (Hopefully)I just ran the test and obviously it failed.
Im getting errors such as Error: The type of the value being assigned to variable "User::AC_START" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Your right though having a little test package is much better!
You are very close...
Change your datetime local variables to string, or convert them explicitly in your source query. I prefer to use strings and convert them later if needed.
Also, the query you are attempting inside the loop is not correct. You cannot query local variables like that. I've created a script task, declared the variables as ReadOnly variables (to make them accessible in the script) and are displaying the variables during each execution. See the attached images.
As you can see...it works. Now you just have plug in the rest of the pieces.
Martin.
November 22, 2011 at 8:01 am
Ive asked the DBA to recreate the job Stored Procedure set at the job level. (Adding this IS package 6 times for each year) BUT Ive got some time between then and now so I will keep going with it.
Thanks for the extra info....
Here we go
November 23, 2011 at 5:32 am
Quick updaet,
I tried and tried but failed to get it working. I really didnt understand the script stuff onm the last post but I nearly got it sorted using ? against the parameters in the loop.
Unfortunately I was getting errors I didnt understand so i have had to ask the DBS to add the package 6 times changing the package parameters in the job.
Thanks for all your help. Hopefully I will get another go at doing it at some point
Debbie
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply