how to export to excel template
Hi again, I am having excel template defined to which I want to export data using SSIS. Issue is that it is not picking up the correct format for newly rows added. 1st rows having column names. 2nd row is having formatted data in the format that I want to use. But when I am exporting data using SSIS (without excel automation) i.e. using "excel destination" , it is not using the format available in the second row, but looks like considering all the exported data in strings. Will you please guide me how can I use existing format to export new rows? Thanks, SurinderI am learning .Net, so corrections/suggestions to my posts are highly appreciated. ----------------------------------------------- surinder singh
July 9th, 2012 6:11am

Could you please put an example/screen print of the spreadsheet? If I understand it correctly, you want to preserve the format in the excel template. In this case, you need to use Script Component/Script Task.Vikash Kumar Singh || www.singhvikash.in
Free Windows Admin Tool Kit Click here and download it now
July 9th, 2012 7:51am

Yes, I want to preserve the format in excel template. In second row I have put sample data and have set the format of each column to the desired format that I need, so now SSIS is starting from 3rd row to put the data. It looks like it is working with this, but it is exporting some blank rows in which none of the columns have any data, so after that blank row format is lost :( I did put Grid viewers on each multicast output to see if there is any row having all the blank columns, but can't find any such row and all the rows in each multicast output is having data :(. Not sure what is going on here, just trying to figure out, any idea/suggestion is welcome here. Thanks I am learning .Net, so corrections/suggestions to my posts are highly appreciated. ----------------------------------------------- surinder singh
July 9th, 2012 7:58am

Any chance you have a carriage return/new line character in one of your columns?Chaos, Disorder and Panic ... my work is done here!
Free Windows Admin Tool Kit Click here and download it now
July 9th, 2012 8:35am

I don't think if I can find out if there is any carriage return/new line character easily as there are around 1200 rows, is there any way to find it? Strange thing is that sometimes it does not insert even single blank row :( and sometimes it inserts blank rows in the starting and sometimes in the middle :( FYI, I am using Multicast to export to same excel having different sheets.I am learning .Net, so corrections/suggestions to my posts are highly appreciated. ----------------------------------------------- surinder singh
July 10th, 2012 12:30am

I found the issue, here is what was causing it and I need guidance: I have joined two excel sheets using Merge Join and sort having one column same (AP_ID). In this merge join I am selecting all the columns from both the tables and just aliasing same column say AP_ID of second table to [AP_ID KRI] Then after combining I am checking if both excels having same AP_ID & [AP_ID KRI], then I am ignoring that row and moving to ERRORS sheet. After few other validations I am again splitting this merged excel file using CONDITIONAL SPLIT because I have to map different field for output for both the excels that I am imported. Then I am sending one excel file's records to one path of split and second excel file to other path and then using separate Multicast on both outputs to send data to 2 sheets in same output excel. so now if I remove one conditional split (!ISNULL([AP_ID KRI]) && [AP_ID KRI] > 0 && FALSE - just by adding && false in the condition so that it will not match with any record and will always be false) , then it is working properly :) and taking the correct format. Data from both the excel files is going to same output template but with different column mappings. I am sure that I am doing it in some wrong way, please direct me in the right direction hereI am learning .Net, so corrections/suggestions to my posts are highly appreciated. ----------------------------------------------- surinder singh
Free Windows Admin Tool Kit Click here and download it now
July 10th, 2012 12:48am

Provide a screenshot of your Data Flow task.My Blog | Ask Me | SSIS Basics
July 10th, 2012 3:27am

I am learning .Net, so corrections/suggestions to my posts are highly appreciated. ----------------------------------------------- surinder singh
Free Windows Admin Tool Kit Click here and download it now
July 10th, 2012 4:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics