从 SSIS 生成 excel 但在每一列中都得到报价?

2021-12-30 00:00:00 excel sql sql-server ssis

我已经从SSIS包中生成并成功.但是每一列都有额外的'(引号)标记，为什么会这样?

I have generated and excel from SSIS package successfully. But every column is having extra ' (quote) mark why is it so?

我的源sql表如下

Name    price    address
ashu    123      pune
jkl     34       UK

在我的 sql table 中，我将 all column 作为 varchar(50) 数据类型.在 Excel Manager 中创建表格时Excel Destination 将所有列都设为相同的 varchar(50) 数据类型.

In my sql table i took all column as varchar(50) datatype. In Excel Manager when it is going to create table Excel Destination took all column as same varchar(50) datatype.

在Data Flow中，我使用了Data Conversion Conversion来防止unicode转换错误.

And in Data Flow I have used Data Conversion transformation to prevent unicode conversion error.

请建议我需要更改的地方以获取 excel 文件中的清晰列.

Please advice where i need to change to get the clear columns in excel file.

推荐答案

您可以创建一个 模板 Excel 文件，在其中指定所有列类型(从常规更改为文本)和标题将需要.将其存储在 /Template 目录中，并将其复制到 SSIS 包中需要它的位置.

You could create a template Excel file in which you have specified all the column types (change to Text from General) and headers you will need. Store it in a /Template directory and have copy it over to where you will need it from within the SSIS package.

在您的 SSIS 包中:

In your SSIS package:

  1. 使用脚本组件将Excel模板文件复制到选择的目录中.
  2. 以编程方式更改其名称并将整个文件路径存储在将在相应数据流任务中使用的变量中.
  3. 为您的 Excel 连接管理器使用表达式生成器.设置要从变量中检索的 ExcelFilePath.
  1. Use Script Component to copy Excel Template file into directory of choice.
  2. Programatically change its name and store the whole filepath in a variable that will be used in your corresponding Data Flow Task.
  3. Use Expression Builder for your Excel Connection Manager. Set the ExcelFilePath to be retrieved from your variable.

相关文章