June 1, 2010 at 2:26 pm
i am trying to loop through table values (primary key of a table) and execute a sql statement on each value (a report that pulls from a few different tables), then export the results to excel. i want to end up with one excel file (report) for each primary key value of the main table.
the package that i started with has an ole db source (the query), a data conversion that lets me export to excel, then a conditional split that will divert the appropriate rows from the main query into one of the many excel destinations that are set up. this works but if there are changes to the main table (that i would like to loop through), then i don't want to have to manually adjust my conditional split and set up a new connection manager. i also don't want it to fail if i get rid of one of the values of the main table.
i am pretty sure that i need to use some sort of for each loop container and pretty sure i need to use variables somewhere.
if someone could direct me to another forum topic that may be helpful or let me know how i should proceed with making this package more efficient i would be most appreciative? i have seen many posts and videos on how to loop through files as a source and have no problem with that. i haven't seen much on how to loop through table values though.
thanks in advance.
June 2, 2010 at 2:33 am
This was removed by the editor as SPAM
June 9, 2010 at 7:41 pm
Hi there
please find attached is the document that can help you with this. It has screenshots and steps as i learnt while doing this... Hope it helps...
Let me know if I can assist in any way
Cheers & Gud luk
vani
June 10, 2010 at 1:04 am
This was removed by the editor as SPAM
June 10, 2010 at 4:49 pm
Hi there
glad to help 🙂
Cheers
June 23, 2010 at 7:55 am
first off let me say thank you! i can tell that once i am able to get this to work it will be a valuable package for me.
i am having some problems getting my namefile var to evaluate. perhaps i have the wrong syntax somewhere? in my case i need to use BuildingID instead of ProdID. my BuildingID variable is scoped at the package level (along with the other 2 vars), and the value is zero. the filepath variable evaluates perfectly. i have tried changing my var data type and it is currently set to char, which is what the actual data type is in the db. not sure if that matters.
@[User::filepath] + "Report-"+[DT_STR,40,1252]@[User::BuildingId]+".xls"
so far it seems that this is my only issue. i may go back and try to create your example from scratch again to make sure i didn't miss anything. if you have any ideas why my namefile variable is not evaluating i would appreciate it.
thanks,
js
June 23, 2010 at 8:13 am
This was removed by the editor as SPAM
June 23, 2010 at 8:27 am
thanks but unfortunately she doesn't like that either. still not evaluating.
definitely seems to be something with the BuildingID var because this evals fine.
@[User::filepath] + "Report-"+".xls"
June 23, 2010 at 11:19 pm
Hi ya
please try the below. just worked it out
Create the following variables
Filepath - Package level - type - String Path - C:\Documents and Settings\Desktop\Results
ProdName - Package Level - Type - String Value - 0
ProductDetailObj - Package Level - Type - Object
Create the package as before
Change the SQL Query in the Execute SQL Task from before to
SELECT pname
FROM products
Leave the result set as is - i.e. Result name - 0 and Variable Name - User::ProductDetailsObj
For Each Loop all remain the same Except Variable Mappings page - Change Variable - User::ProdName and leave the Index to 0 instead of User::ProdID and Index to 0
Execute SQL Task and Data flow task in the For Each loop remain the same
The connection manager for Excel changes - Right click Excel Connection Manager - Select Properties
Go to Expressions - Select Excel File Path - Enter the following - @[User::filepath] + (DT_STR, 20, 1252) @[User::ProdName] + ".xls"
Save and Run the package...
Ps: Can you possibly change your column in the table from Char to Varchar may be that can help...
Try this in your local machine first and let me know how you go...
Gud Luck
Cheers
Vani
June 23, 2010 at 11:20 pm
This was removed by the editor as SPAM
June 24, 2010 at 2:14 pm
Hei
how did it go... have you got it working 🙂
June 25, 2010 at 11:19 am
the package doesn't make it that far. not sure if it needs to get that far and break in order for me to obtain anything for that. i'm still a rookie and not used to using breakpoints. i know what they are, just not all that they can do.
my package is erring on the first step where the first SQL task goes into the foreach loop and is telling me
The type of the value being assigned to variable "User::ProdID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
my ProdID variable is set to Int32. i have also tried it as char but that fails with the same message. does the variable type have anything at all to do with the data type in the table which it resides?
June 27, 2010 at 9:03 pm
Hi Justin
How did it go... is the copy of the file i sent you working
June 27, 2010 at 9:23 pm
vani_r14 (6/9/2010)
Hi thereplease find attached is the document that can help you with this. It has screenshots and steps as i learnt while doing this... Hope it helps...
Let me know if I can assist in any way
Cheers & Gud luk
vani
Cool... I'm just starting to teach myself SSIS and this will likely come in very handy. Thanks, Vani.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2010 at 1:20 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply