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.

Option	Description
Excel File	Defines the path to the Excel file to be refreshed. Note:  .xlsx and .xlsm files are supported.
Refresh All Connections and Sheets?	Toggle that selects all Power Query Connections and Pivot tables within the Excel file.
Execution Timeout (Seconds)	Defines the allotted time for the refresh to execute in seconds.                         Note:  The timeout cannot be used for connections that prompt users for their credentials.

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

Option	Description
Excel Connection Manager	Create a new or select an existing connection to an Excel worksheet.
Choose Worksheet	For Excel files that use many worksheets, you can choose which one to extract data from. Note:  You can configure the Excel source component to use sheet names that are dynamically set by a variable with an Expression in your Data Flow Task.
First Row Has Header Names	This option is selected if the first row contains headers. If the first row contains data, remove this option and configure the columns in the Columns tab.
Region / Range Settings	Choose from the following: Region - Use the Row and Column values below to define the data range. Named Range - Select from named ranges created in an Excel file. When Named Range is selected, a dropdown box appears. Choose from the available named ranges within the document.
Rows	Determines the starting and ending row. -1 denotes using all available rows. Note:  When the first row contains headers, it counts as the first row when starting your range at row 1.
Columns	Determines the number of columns in the output. -1 denotes using all available columns.
Output Format	Formatted - Select this option if the Excel file's data is formatted and you want to keep it in the output. Raw Value - Ignores formatted data and the original raw values are output.
Show Preview	Selecting this option enables and displays the preview based on the component's configuration.

Columns

Option	Description
Code Page	Identifies character encoding.
Column Name In File	Identifies the name of the column coming from the Excel file.
DataType	All data types default to DT_WSTR but can be edited for output to be integers, boolean, strings, etc.
Excel Column Index	Defines the numeric order of the column.
Include In Output	Selected by default, determines whether or not the column is included in the output.
Length	Defines the number of characters for string and wstring data types.
Output Column Name	Defines the name of the column in the output. Column names can be edited here.
Precision	Defines the number of digits for numeric data types.
Scale	Defines the number of digits after the decimal for numeric data types.
Refresh Excel Columns	Refreshes the column information if the Excel document is changed while the component is open.

Excel Destination

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

File Format

Option	Description
Excel Connection Manager	Create a new or select an existing connection to an Excel worksheet. For more information, see the Excel Connection Manager.
Choose Worksheet	Select which worksheet to load data to.
Existing Sheet Data	Choose from the following: Clear - Clears all existing and residual data before writing to the spreadsheet. Append - Adds data after pre-existing data in the spreadsheet. Previous data will remain in the file/worksheet. Overwrite - Overwrites the existing data but keeps residual data. (Example: If 100 rows are inserted into a spreadsheet with 200 rows, rows 1-100 will be overwritten and rows 101-200 will remain.)
Row Start	Determines the row number to begin writing.
Column Start	Determines the column number to begin writing.
Create Table	Select this option to add table formatting.
Table Name	Defines the name of the table.
Create Table Auto Filters	Select to create auto filters.

Columns

Option	Description
Excel Column Mapping	Maps the column to the excel file's cell.
Excel Columns	Edits or renames a column that is written to the Excel file.
Include In Spreadsheet	This option is selected by default. Clear this option to prevent the column from being output to the Excel file.
Style	Columns can be formatted using styles within Excel. Please see the Excel Style page for more information.
Refresh Excel Columns	Refreshes the available columns that can be selected in the Excel Column Mapping property using data from the destination spreadsheet. If no columns currently exist, you will be shown the letter reference of the excel column (e.g. A, B, C, etc.)

Excel Destination Column Styles

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

Format Code	Description
General	General number format.
#	Digit placeholder that represents optional digits and does not display extra zeros.
0	Digit placeholder that displays insignificant zeros.
?	Digit placeholder that leaves a space for insignificant zeros but doesn't display them.
@	Text placeholder.
. (period)	Decimal point.
, (comma)	Thousands separator. A comma that follows a digit placeholder scales the number by a thousand.
\	Displays the character that follows it.
" "	Display any text enclosed in double quotes.
%	Multiplies the numbers entered in a cell by 100 and displays the percentage sign.
/	Represents decimal numbers as fractions.
E	Scientific notation format.
_ (underscore)	Skips the width of the next character. It's commonly used in combination with parentheses to add left and right indents, _( and _) respectively.
* (asterisk)	Repeats the character that follows it until the width of the cell is filled. It's often used in combination with the space character to change alignment.
[]	Creates conditional formats.

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

Symbol	Description
+ and -	Plus and minus signs
( )	Left and right parenthesis
:	Colon
^	Caret
'	Apostrophe
{ }	Curly brackets
< >	Less-than and greater than signs
=	Equal sign
/	Forward slash
!	Exclamation point
&	Ampersand
~	Tilde
	Space character

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

Code	Description	Example
0	Digit placeholder that displays zeros.	#.00 - displays two decimal places. A cell that contains 1.2 displays as 1.20.
#	Digit placeholder that represents optional digits (does not display extra zeros.)	#.## - displays up to two decimal places. 1.2 displays as 1.2 1.236 displays as 1.24
?	Digit placeholder that leaves a space for insignificant zeros on either side of the decimal point but doesn't display them. (Often used to align numbers in a column by decimal point.)	#.??? - displays a maximum of three decimal places and aligns numbers in the column by decimal point.
@	Text placeholder.	0.00; -0.00; 0; [Green]@ - applies the green font color to text values.

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:

General	#,###	#,	#.00,	#.00,,
50	50		.05	.00
500	500	1	.50	.00
5000	5,000	5	5.00	.01
55500	55,500	56	55.50	.06
555500	555,500	556	555.50	.56

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

General	#.00, \K	#,###.0, \K	#.00,, \M	#,###.0000,, \M
50	.05 K	.1 K	.00 M	.0001 M
500	.50 K	.5 K	.00 M	.0005 M
55500	55.50 K	55.5 K	.06 M	.0555 M
5555000	5555.00 K	5,555.0 K	5.56 M	5.5550 M
5555000000	5555000 K	5,555,000.0 K	5555.00 M	5,555.0000 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

Format	Description	Example (January 1, 2018)
m	Month number without a leading zero	1
mm	Month number with a leading zero	01
mmm	Month name in short form	Jan
mmmm	Month name in full form	January
mmmmm	Month as the first letter	J
d	Day number without a leading zero	1
dd	Day number with a leading zero	01
ddd	Day of the week in short form	Mon
dddd	Day of the week in full form	Monday
yy	Last 2 digits of the year	18
yyyy	All 4 digits of the year	2018

Custom Time

Format	Description	Example (January 1, 2018)
h	Hours without a leading zero	0-23
hh	Hours with a leading zero	00-23
m	Minutes without a leading zero	0-59
mm	Minutes with a leading zero	00-59
s	Seconds without a leading zero	0-59
ss	Seconds with a leading zero	00-59
AM/PM	Periods of the day	AM or PM