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.
I am able map the header names, in Excel Destination Editor, to the SQL table headers, by writing the SQL command:
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?
Any help, to solve this, is appreciated. Thanks in advance!
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.
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:
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.
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.
