August 25, 2011 at 6:47 am
Hi,
[font="Verdana"]I am New to this Forum and i hope i will get some valuable infirmations from my senior members. 🙂 [/font]
Having 2 yrs of exp in SQL,now started learning SSIS am New to this.
I need to exec SP which is having 2 parameters,
Exec Testsp @Cid,@Region
This sp is having 10 coulmns as outupt
The Task which was assigned to me is i need to take the output from the sp and save it in excel file.
If it is SQL then i can run Results to file option and save it but how to start with SSIS.
10 Companies are there i should not pass the companyid everytime it should get the file or from the table from DB
Same for Region 4 Region are there i should not pass the Regionid everytime it should get the file or from the table from DB
Which Task in control flow need to be used and which transformations need to be used in dataflow
How this could be accomplished.
Thanks
Yuvi.
August 26, 2011 at 11:13 am
So you want, for each of the 10 companies and four regions, an output? In other words, a cross join of the two, resulting in a total of 40 outputs?
In that case,
Step 1: Create an Execute SQL Task in your Control Flow.
-Choose ResultSet as Full Result Set
-Set up your Connection object to point to your database
-Leave SQLSourceType as Direct input
-For SQL Statement, write your query that will merge the two tables together. You want each row of your output to contain one company-region pair
** IMPORTANT **
-Go to the Result Set tab, and add a variable as your output. The variable should be of type Object
Step 2: Create a ForEach Loop Container in your Control Flow. Link the Execute SQL Task from Step 1 to the input of this container
-Choose Foreach ADO Enumerator from the Collection tab
-Under Enumerator Configuration, select the variable you created in Step 1 to store the output.
-Choose Rows in the first table as your Enumeration Mode
** IMPORTANT **
-Go to the Variable Mappings tab, and create two variables. One will store the output with Index 0, the other with Index 1. These values will be your company and region for the iteration. Choose type String for these variables.
Step 3: Add a Data Flow Task to the ForEach Loop Container.
-Within the Data Flow Task, add an OLE DB Source.
-Choose SQL Command as your data access mode
-Enter the stored proc query, in the format: EXEC [stored proc name] ?, ? -- Note the question marks - those are important, as they hold the input parameters to the stored proc
-Click the Parameters button, and map the parameters using the two variables you created in Step 2
-Go to the Column Mappings and make sure you have the columns you want
-Add an Excel Destination to your Data Flow Task. Link it from the OLE DB Source.
** IMPORTANT **
You're probably going to want to have several different excel file outputs.
To do this, create your first OLE DB Connection Manager as you normally would for an Excel Destination - from the Excel Destination task, you can click "New" to create a new connection manager.
This will let you set up the schema correctly for each flow.
Once you're done configuring it, go to your Connection Managers tab in your Data Flow Task, and select the Excel Connection Manager you created.
In there, go to the Properties window, and select the ExcelFilePath property. Put in an expression that defines a unique name for this particular report. Could be something simple like, appending the company and region to the file name. Also, select Delay Validation property as true.
This will let you create new files for each report.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply