looping through table values, exporting to excel
-
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.
-
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
-
Hi there
glad to help
Cheers
-
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
-
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"
-
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
-
Hei
how did it go... have you got it working
-
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?
-
Hi Justin
How did it go... is the copy of the file i sent you working
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Justin
How are you going...
The package i sent you should work ones you specify the database connection and the variable path...
Let me know if you need help
Cheers
-
Hi Jeff
No worries... Am learning it myself...
Hope you enjoy learning it
Cheers
-
i took the package you sent and all i did was adjust the connection managers since i don't have the same paths as you did. i am getting this error.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "CREATE TABLE `Excel Destinationlo` (
`Copy of pid` INTEGER,
`Copy of pname` NVARCHAR(50)
)" failed with the following error: "Table 'Excel Destinationlo' already exists.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at Foreach L
This error leads me to believe that i need to delete the excel sheet so that it will be created when the package runs. however when i try to run the package with no Excel Destinationlo on the .xls then i get this error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [Excel Destination [738]]: An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [Excel Destination [738]]: Opening a rowset for "Excel Destinationlo" failed. Check that the object exists in the database.
Error at Data Flow Task [DTS.Pipeline]: "component "Excel Destination" (738)" failed validation and returned validation status "VS_ISBROKEN".
any ideas here?
-
Hi Justin
Lets start over with the excel - Create one spreadsheet and leave the name as default
Open the connection manager in ssis - remove the setting in the expression task
and disable the execute sql task in the for each loop editor and try running hte package as is... it should not work but will give you an idea that things are ok... It will not work because there is a create table statement which cant create the same table over and over again in the same sheet.
Open the spreadsheet again remove the sheet created and leave the one sheet with default name
Enable the Execute sql task in the For each loop editor
add the expression back in the Excel connection manager
Save and try again...
Just so you know it took me two weeks appx to work this out because of all errors... I had help from a member called Anu in the forums... So please dont give up and its almost there...
please send me a copy of the package and i will help as well
Cheers
Vani
-
Hi Justin
how are you going with this
Let us know if you need help
Cheers
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply