Task Factory Excel

Excel Connection Manager

This connection manager is used by the Excel Source and Excel Destination components. Both components are located in the data flow. To select a file, select the ellipsis > navigate to and then select the Excel file.

Excel Power Refresh Task

Task Icon	Task Description
	The Excel Power Refresh Task is a control flow component that refreshes an Excel file with Power Query connections and Power Pivot tables.

Power Query Connections	Power Pivot Sheets
Select individual connections to be refreshed in the selected Excel file.	Select individual Power Pivot tables to be refreshed in the selected Excel file.

Configuring Excel Power Refresh for SQL Agent Jobs

Excel Power Refresh within a SQL Agent Job must be done by using a proxy account. Please use the following steps prior to configuring your SQL Agent Job:

1. Create a credential based on your login (found within the Security folder in SSMS):
2. Create a proxy account based on the previously created credential. Select the SSIS Package Integration Services Package subsystem:
3. Set the job step to run as the proxy account:
4. Start the job.
5. If you receive the following error: 

Code: 0x00000000     Source: TF Excel Power Refresh Task ExecuteTask Failed:     Description: Microsoft Excel cannot access the file <path>.xlsx'. There are several possible reasons:    ? The file name or path does not exist.  ? The file is being used by another program.  ? The workbook you are trying to save has the same name as a currently open workbook.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:28:39 PM  Finished: 1:28:44 PM  Elapsed:  4.735 seconds.  The package execution failed.  The step failed.

This issue can be resolved by adding a Desktop folder to the following directories:

• C:\Windows\SysWOW64\config\systemprofile\
• C:\Windows\System32\config\systemprofile\

6. Restart the job.

Excel Source

Source Icon	Source Description
	The Excel Source connects to an existing Excel file and extracts its data.

File Format

Columns

Excel Destination

Destination Icon	Destination Description
	The Excel Destination connects to a new or existing Excel file and writes data to it.

File Format

Columns

Excel Destination Column Styles

The most common and useful implementations of Excel format codes are:

The following characters can be included in Excel custom format codes without the use of backslash or quotation marks:

Excel Number Format

Excel number format consists of 4 sections of code separated by semicolons in the following order:

POSITIVE; NEGATIVE; ZERO; TEXT

Example:In this example, positive numbers display two decimal places and a thousands separator. Negative numbers appear the same as positive numbers but enclosed in parenthesis. Zeros are replaced as dashes, and the text displays in the magenta color.

Digit and Text Placeholders

Rounding Numbers

Microsoft Excel separates thousands by commas if a comma is enclosed by any digit placeholders - pound sign (#), question mark (?) or zero (0). If no digit placeholder follows a comma, it scales the number by thousand, two consecutive commas scale the number by million, and so on.

Example:

Text and Spacing Formatting

To display both text and numbers in a cell, do the following:

To add a single character, precede that character with a backslash (\).

To add a text string, enclose it in double quotation marks (" ").

Example: Numbers that are rounded by thousands and millions - add \K and \M to the format codes, respectively:

To display thousands: #.00,\K

To display millions: #.00,,\M

Temperatures

• #"°F" displays Fahrenheit (Example: 85°F).
• #"°C" displays Celsius (Example: 23°C).

Display Leading Zeros

• Adds the number zero (0) as a placeholder to indicate the number of leading zeros.
• Example: Adding 00000 to the style displays the number 55 as 00055.

Percentages

• Adds the percent sign (%) to the style format box.
• Example: #% displays the number 0.08 as 8%.

Fractions

• For decimal numbers to appear as fractions, add the forward slash (/) to the style format box.
• Example: #/# displays the number .5 as 1/2.

Scientific Notation

• To display numbers as scientific notation, add the capital letter E to the style format box.
• Example: 00E+00 - displays 1,500,500 as 1.50E+06.

Indents

• Some prefer a cell's contents not position against the cell's border. To add an indent, use the underscore (_).
• _(  adds indent from the left border.
• _) adds indent from the right border.

Font Color

• Adding the color surrounded by square brackets ([ ]) will format the font to the specified color. (: the color code must be the first item in the section. Also, only the following colors can be used - Black, Green, White, Blue, Magenta, Yellow, Cyan, and Red.)
• Example: [Blue] $#,##0.00 displays the number 25.3 as $25.30.

Repeat Characters

To repeat a specific character so that it fills the column width, use the asterisk (*) before the character to be repeated. 

Example: *- makes the cell with the number 555 appear as |------------555| . 

Alignment

• To align numbers left in a cell, type an asterisk and a space after the number code. Example: "#,###* " (Exclude the double quotes when adding the format.)
• Custom Number Formats Based on Conditions.
• To apply conditions that must be met for a custom style, enclose the condition within square brackets. 
• Example: [Red] [<100] ; [Blue] [>=100] displays numbers as 49 and 184.

Custom Date

Custom Time