跳过行：将数据从SSIS导出到excel文件中

I am trying to export data from a SQL server database into an excel file using SSIS. I want the data to get inserted from the 6th row and 5th row has headers.

我正在尝试使用SSIS将数据从SQL Server数据库导出到excel文件中。我想从第6行插入数据，第5行有标题。

I am able map the header names, in Excel Destination Editor, to the SQL table headers, by writing the SQL command:

通过编写SQL命令，我可以将Excel目标编辑器中的标题名称映射到SQL表头：

SELECT * FROM [Sheet1$A5:EC5]

But still, when I execute the package, the data gets inserted from the 2nd row How can I start the insertion from 6th row?

但是，当我执行包时，数据从第2行插入如何从第6行开始插入？

Any help, to solve this, is appreciated. Thanks in advance!

任何帮助，解决这个问题，表示赞赏。提前致谢！

3 个解决方案

#1

Add the blank rows to the dataset in OLE DB source in SSIS. I assume that your columns in your database are named Header1, Header2 and Header3. Replace your OLE DB source query with this query:

将空行添加到SSIS中OLE DB源中的数据集。我假设您的数据库中的列名为Header1，Header2和Header3。用此查询替换OLE DB源查询：

 select ' ' as Header1, ' ' as Header2, ' ' as Header3
 UNION ALL
 select ' ', ' ', ' '
 UNION ALL
 select ' ', ' ', ' '
 UNION ALL
 select ' ', ' ', ' '
 UNION ALL
 select ' ', ' ', ' '
 select Header1, Header2, Header3 from Your_SQL_SERVER_Tabl

You may need to cast your columns to varchar if they are of other types.

如果列为其他类型，则可能需要将列转换为varchar。

#2

I've had a similar issue trying to export to Excel from SSIS. I ended up going the third-party route by purchasing the "Export Excel Task" from ZappySys. It allows you to specify the cell offset to start at.

我有一个类似的问题试图从SSIS导出到Excel。我最终通过从ZappySys购买“Export Excel Task”来进入第三方路线。它允许您指定要从中开始的单元格偏移量。

#3

You need to use "OpenRowset" properties for excel source, go to the properties page for excel source, set the "OpenRowSet" as "$A6:D", then it should solve your problem.

你需要为excel源使用“OpenRowset”属性，转到excel源的属性页面，将“OpenRowSet”设置为“$ A6：D”，然后它应该解决你的问题。

#1