Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skishie
Starting Member
4 Posts |
 Posted - 2010-02-11 : 00:17:53
|
| hello,i just want to ask if it is possible to delete a header row of excel using ssis. i'm currently exporting records from sql server to ms excel using dynamic filename. however, i cannot delete the header row every time i generate excel file. i've already change the HDR property to NO. but the result is also the same. i suspect that the header row is being created every time it passes through the destination excel since i create table and fields from there. this is my first time using ssis. i hope someone can help me with this problem.and thank you, in advance for any help you can give |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-11 : 02:45:46
|
| what do you mean by dynamic filename? Are you using a global variable to determine excel file name?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
skishie
Starting Member
4 Posts |
Posted - 2010-02-12 : 01:14:36
|
| no, I only based my filename with date and time. example, bit_sampleFile201002121404.xlsit's already been working. however, i cannot delete the header row which includes the field name of the database. it's part of the procedure and i cannot find any solution on how to delete the header.the only thing that i can think of now is to use script task. however, i cannot find the correct library. Microsoft.Office.Interop.Excel.dll 10.4504.0 was the one i downloaded yesterday. but i just found out that it was not compatible with the Microsoft.Office.Interop.Excel 11.0.0.0 that is currently installed in my pc.is there any other way aside from scripting that i can delete the header row of the generated excel? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-12 : 01:33:57
|
quote:
Originally posted by skishie
no, I only based my filename with date and time. example, bit_sampleFile201002121404.xlsit's already been working. however, i cannot delete the header row which includes the field name of the database. it's part of the procedure and i cannot find any solution on how to delete the header.the only thing that i can think of now is to use script task. however, i cannot find the correct library. Microsoft.Office.Interop.Excel.dll 10.4504.0 was the one i downloaded yesterday. but i just found out that it was not compatible with the Microsoft.Office.Interop.Excel 11.0.0.0 that is currently installed in my pc.is there any other way aside from scripting that i can delete the header row of the generated excel?
you could add a rownumber to your file input and then add a conditional task to get rid of rownumber=1 row------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
skishie
Starting Member
4 Posts |
Posted - 2010-02-12 : 23:35:33
|
| Hi Visakh16!Thank you very much for your immediate reply...by the way, where will i input the rownumber function? my structure was all too simple, execute the db query, create the table and convert the retrieved db records into excel file through data flow. And in creating the table, i queried the command like this:CREATE TABLE `Transaction` (`Field1` NVARCHAR(25), `Field2` NVARCHAR(50), `Field3` NVARCHAR(1), `Field4` NVARCHAR(10), `Field5` NVARCHAR(8), `Field6` NVARCHAR(3), `Field7` NVARCHAR(10))you mean, i will include the rownumber command here or in other properties of the ssis project? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-13 : 10:03:09
|
| you can include rownumber transformation in ssis and add it as a new column in input flow.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
remove header row