All geek questions in one place

Skip lines: export data from SSIS to excel file

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

enter image description here

I can map the header names in the Excel Destination Editor to the headers of the SQL tables by writing the SQL command:

SELECT * FROM [Sheet1$A5:EC5]

But still, when I execute the package, the data is inserted from the second row. How to start the insertion from the 6th row?

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

+6
source share
3 answers

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

  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 drop your columns in varchar if they belong to other types.

+2
source

I had a similar problem trying to export to Excel from SSIS. I switched to a third-party route by purchasing the "Export Excel Task" from ZappySys. It allows you to specify the cell offset to start with.

0
source

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

0
source

Source: https://habr.com/ru/post/1015715/

More articles:


All Articles