SSIS Exporting to Multiple Excel Files

Question

SSIS Exporting to Multiple Excel Files

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
December 18, 2017 at 2:31 pm

#326942

Hi everyone
I have to send invoices to our customers each month which is a time consuming manual process ;(
The invoices are created by running the following SQL
SELECT
CustomerID,
CustomerName,
ProductID,
ProductName,
Store,
State,
SalePrice
FROM dbo.SALES
WHERE CustomerID = '1234'
Currently, I have to painstakingly run the script for each customer, paste the output from SSMS into Excel and save the file as [Customer name].Monthly Invoice [Month Name and Year].xls
I know there must be an SSIS solution but really need help in trying to put this together - any pointers, articles, script would be greatly appreciated.
BO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Take a look at the For Each Loop container. You'll need to set up a number of package variables to handle things like the varying names of the spreadsheets, and you can probably create a stored procedure to handle the query for each customer based on the customer id value. You can generate those as the query that sustains the For Each Loop container.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Have you considered using SSRS and putting a page break based on customer id and save as a PDF or as Excel?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ponnuru SSC Enthusiast Points: 167 More actions · Answer 3

Hi Byron,
You can do it by using For each loop container.Basically divide the whole work into two parts.First Generate the excelsheet and create sheet names using Execute SQL Task and insert the data into excel sheet using DataFlowTask.

1.First list out all customer names and save it to Object variable using ExcuteSQL Task.
2.Next step,add forreach loop container and select ForEachAdo Enumarator.
3.Go to -->Variable Mapping --> Map Customer name to one variable.
4.Take execute SQl task inside Foreach loop container. -
-> Choose connectionType --Excel
   --> Connection -- create excelconnection manager and select that connection.
   --> SQLSourceType -- Variable
   -- > Create a variable and build expression like below
"CREATE TABLE `"+(DT_STR,20,1252) @[User::VariableName] +"` (
`Column1` NVARCHAR(255),
`Column2` NVARCHAR(255),
`Column3` NVARCHAR(255),
`Column4` NVARCHAR(255),
`Column5` NVARCHAR(255)
)"
This will create a excelsheet with customername as sheetname.

5.Create DataFlowTask and
--> Source query pass customer name in where clause
-- Target as excelsheet and sheet name UserName.

You can create excelsheet dynamically by building expression and passing that expression value to excelconnection manager.

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 4

Thanks for the responses guys.

I have gone with your suggestion Ponnuru - thanks for setting it out in such detail.

BO