Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to export excel sheet with SPECIAL formats Expand / Collapse
Author
Message
Posted Wednesday, January 02, 2013 9:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:35 AM
Points: 37, Visits: 133
Hi,
I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it using Scripts via script component in SSIS but my question is there any other way in doing it so.
Please explain it if there is a way in doing it so.
Thanks in advance:)
Post #1402164
Posted Thursday, January 03, 2013 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 2:44 PM
Points: 18, Visits: 104
manibad (1/2/2013)
Hi,
I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it using Scripts via script component in SSIS but my question is there any other way in doing it so.
Please explain it if there is a way in doing it so.
Thanks in advance:)


What do you mean special Formats?
Post #1402429
Posted Thursday, January 03, 2013 5:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.

One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1402621
Posted Thursday, January 03, 2013 10:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:35 AM
Points: 37, Visits: 133
Special formats for example...in excel sheet if you choose "Special format" under that "Social security number" the data specified in such a column would be always in SSN format..
I think i am clear...shld i explain more...
:)
Post #1402675
Posted Friday, January 04, 2013 4:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
opc.three (1/3/2013)
The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.

One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.


+1 to this second idea.

Or, in a similar vein, have an Excel template which contains a 'Format' button & associated VBA code to allow the formatting to be done by whoever opens the Excel file after the export has completed



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1402791
Posted Monday, January 07, 2013 10:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:35 AM
Points: 37, Visits: 133
opc.three (1/3/2013)
The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.

One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.



Using file system task i was able to copy the file template from one location to other.Using data flow task i manipulated the source using source and then i coded to execute the file system task.
In dataflow task,
I sorted the data and then i connected with excel destination.Where the destination is the template.
I am getting the output without special formats.
Data flow task and file system task was completed successfully.


What to do for rectifying the above and obtaining the desired output.
Post #1403743
Posted Monday, January 07, 2013 10:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
The formatting will be pre-set in the "template" file. Open your "template" in Excel, adjust the formatting so it appears as you would like (e.g. bold column headers, background colors, etc.), save the "template" xlsx file and then close Excel. Then re-run your SSIS package. The outcome should be that the output file, the one you loaded data to, should also contain the formatting taken from the "template".

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1403752
Posted Monday, January 07, 2013 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:35 AM
Points: 37, Visits: 133
I will list out the things what i have done.
1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))
2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.
3.Package will be executed in this order Dataflow task->File system task

Note:Format.xlsx was placed after assigning the special formats to the worksheet.

but still i am getting the exported file with general format.I dint get it with special formats.

Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.
Post #1403765
Posted Monday, January 07, 2013 11:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
manibad (1/7/2013)
I will list out the things what i have done.
1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))
2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.
3.Package will be executed in this order Dataflow task->File system task

Note:Format.xlsx was placed after assigning the special formats to the worksheet.

but still i am getting the exported file with general format.I dint get it with special formats.

Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.


Pre-requisite: open Format.xlsx (template file) in Excel and apply formatting as needed. Save to disk in original location as original name Format.xlsx.

SSIS Workflow:

1. In file system task, set to Copy source path Format.xlsx (template file) to destination file name is destiny.xlsx (copy of template file). Here the destination file is the copy of template file.
2. In data flow task,OLE DB source->Sort->Data conversion->Excel destination (target is destiny.xlsx (copy of template file))
3. Package will be executed in this order File system task->Dataflow task


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1403771
Posted Wednesday, January 09, 2013 2:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:35 AM
Points: 37, Visits: 133
opc.three (1/7/2013)
manibad (1/7/2013)
I will list out the things what i have done.
1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))
2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.
3.Package will be executed in this order Dataflow task->File system task

Note:Format.xlsx was placed after assigning the special formats to the worksheet.

but still i am getting the exported file with general format.I dint get it with special formats.

Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.



Pre-requisite: open Format.xlsx (template file) in Excel and apply formatting as needed. Save to disk in original location as original name Format.xlsx.

SSIS Workflow:

1. In file system task, set to Copy source path Format.xlsx (template file) to destination file name is destiny.xlsx (copy of template file). Here the destination file is the copy of template file.
2. In data flow task,OLE DB source->Sort->Data conversion->Excel destination (target is destiny.xlsx (copy of template file))
3. Package will be executed in this order File system task->Dataflow task


after performing the above steps too i am unable to achieve my goal.
I am unable to find where is the error.
actually special format is nothing but the formats such as Zipcode,zipcode+4,Home phone number and social security number...
this is what i have to achieve...
Post #1405029
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse