SSIS根据存储过程的输出编写一个工作簿的不同工作表 [英] SSIS to write different worksheets of one workbook as per output from stored procedure

问题描述

大家好，

我有下面的表格
产品
ID，名称，费用

ProductMasterList
名称

和一个SP
具有产品名称参数的ProductSearch并根据产品名称给出结果


我想创建一个SSIS程序包，该程序包将为每个产品创建一个具有工作表的excel工作簿，并且每个产品工作表将具有与产品详细信息(标识，名称，成本)相关的数据

请指导我如何实现.

Hello All ,

I have below tables
Product
Id,Name,Cose

ProductMasterList
Name

and one SP
ProductSearch having parameter for productname and give outpur depend upon productname


I want to create one SSIS package which will create one excel workbook having sheets for each product and each product sheet will have data related to details of product (Id,Name,Cost)

Please guide me how I can achieve that.

推荐答案

1.首先创建一个变量"ProductList"作为对象类型.
2.创建一个字符串变量"ProductName"
3.获取生成您要传递给SP的产品的不同列表的SQL任务.并将完整行集"的任务结果集分配给"ProductList"变量
4.使用Foreach ADO枚举器获取带有集合的For Each循环
选择"ProductList"作为"ADO Object源变量".在变量映射上，选择索引为0的"ProductName".
5.在其中添加SQL任务并在Excel上创建表.还可以使"SQL语句源"的表达式成为动态表名称.示例表达式可以是:

1. First Create a variable "ProductList" as Object Type.
2. Create a string variable "ProductName"
3. Get SQL Task that produces Distinct List of products that you would like to pass to SP. And assign the task result set of Full Row Set to "ProductList" variable
4. Get For Each Loop with collection as Foreach ADO Enumerator
Select "ProductList" as the "ADO Object source variable". On the variable mappings select "ProductName" with index 0.
5. Add SQL Task inside and Create Table on Excel. Also make expression for "SQL Statement Source" to get table name dynamic.. A sample expression can be :

"CREATE " + @[User::ProductName]   + "\n" +
  "("+"\n" +
"ID Nvarchar(255) " + "\n" +
"Name Nvarchar(255) " +"\n"+
"Cose Nvarchar(255) " +"\n"+
")"

6.添加DataFlow Task并将延迟验证"设置为true
7.添加Source OLEDB并将SQL Command设置为:
EXEC sp_Name吗?
现在将参数映射到ProductName变量.
8.创建Excel目标并将数据访问模式"设置为表或变量的视图名称"，然后选择ProductName变量.
映射所需的列.

6. Add DataFlow Task and set Delay Validation as true
7. Add Source OLEDB and set SQL Command as :
EXEC sp_Name ?
Now map paramter to ProductName variable.
8. Create Excel Destination and set "Data Access Mode" as "Table or view name from variable" and select ProductName variable.
Map the required columns.

这篇关于SSIS根据存储过程的输出编写一个工作簿的不同工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！