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 |
|
h_zulfi@yahoo.com
Starting Member
12 Posts |
 Posted - 2008-02-26 : 05:34:42
|
| Hi gurus,I got a pre-formated excel sheet as destination. The header is formatted with color.The requirement is to populate the excel sheet with different data type (name(string), no (int), sal(int), age(int)) from second row onwards. The source data is a SQL table and I am using SSIS to export data to the above excel destination.Problem: Scenario 1: Using SSIS data flow, populate the excel sheet. In this case, the format of the first row is used by all the new inserted records. So, I am not able to format as per the data type requirement.Scenario 2: To populate data with required data types, I need to format the second row with the specified data types and then export data into the excel in the SSIS data flow. In this case, there is a blank row in the second row position as shown in the picture:(see the picture on url)[url]http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040908106882370[/url]To meet the requirement, I need to remove the blank row in the excel destination. It should look like below:[url]http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040912401849682[/url]Is there any other method to achieve the export of data meeting the specified requirement?If no, then is there any method to remove the blank row from excel apart from using com objects to remove the blank row and shift cells up right.Any thought and help in this regard would be much appreciated.Cheers,Zulfi |
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-02-26 : 10:33:05
|
| I think you'd have to use a Script Task to do this after exporting the data. The other option is to pull the data from Excel instead of pushing it from SQL Server. Is doing the scripting in Excel an option for you? |
 |
|
|
h_zulfi@yahoo.com
Starting Member
12 Posts |
Posted - 2008-02-27 : 04:33:42
|
| I could use script task to remove row in excel sheet. But to access excel, com inter operabilty objects are required. I cant use any other dll's except .net in production environment. |
|
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-02-27 : 10:34:37
|
| Can you put the code in the Excel file? It's a simple VBA exercise to open an ADO recordset and pull the records into your Excel spreadsheet as needed. |
|
|
|
|
|
|
|
|
Remove a row in excel