Multiple Queries to excel with Variables
Hi, I'm pretty new to SSIS and don't know how to do what i want. 1. I have 9 queries that need to export each result set into a seperate worksheet in the same workbook in excel. I created a seperate DFT for each query and the package runs like i want it to. should i use seperate DFTs or build one DFT with multiple sources and destinations? although this will cause each query to run simultaneously rather than concurrently as they do now? 2. each query uses the same variables (store id) and (sales year). How do I implement those into the package so each DFT uses the variables in the WHERE statements 3. How do i delete the excel workbook at the start of the package? right now each time it runs it just adds the new data at the end of the worksheet.
October 23rd, 2012 4:16pm
1. Keep different dataflows for each query. Putting too many sources and destinations in one dataflow can affect performance. If you want the dataflows to run in parallel, just do not connect them with the green arrows. 2. Create a dynamic SQL statement in a variable. Make sure to put the variable property EvaluateAsExpression to true. For example: "SELECT * FROM myTable WHERE StoreID = " + @[User:StoreID] + " AND SalesYear = " + @[User:SalesYear] In the OLE DB Source, you can select the option to select the statement from a variable.MCTS, MCITP - Please mark posts as answered where appropriate.
October 23rd, 2012 4:34pm
Do I have to create a different variable for each DFT Source query i created? Also, when i set to the OLE DB source to Command with Variable and try to run it, it says must declare variable although I've already declared it
October 28th, 2012 7:38am
If you want to run the dataflows in parallel: yes. It's not possible for a variable to have multiple values at the same time. Regarding the source error: make sure your variable has the correct scope.MCTS, MCITP - Please mark posts as answered where appropriate.
October 28th, 2012 7:50am
How do i set the variable so the query changes each time it reaches a new DFT? the variable varSQL is set to package and the value is the SQL statement for the first DFT.
October 28th, 2012 8:02am
You need to update the variable before the next dataflow is started. I'm not sure how you created the expression on the variable, but if for example SalesYear needs to change, you could run an Execute SQL Task that sets the year. SELECT 2012 AS SalesYear Configure the task to return a resultset and in the Result Set pane configure it to overwrite the SalesYear variable. Alternatively you could update the variable with a script task.MCTS, MCITP - Please mark posts as answered where appropriate.
October 29th, 2012 2:17am