Documentation forTask Factory

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.

Task Factory Excel Connection Manager Connection Settings

Excel Power Refresh Task

Important: 

  • Microsoft Office must be installed on the environment where the package is located.
  • Excel Power Refresh is available for SQL versions 2012 and higher.
Task Icon Task Description
Task Factory Excel Power Refresh Task Icon 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.

Task Factory Excel Power Refresh Data Connections

Select individual Power Pivot tables to be refreshed in the selected Excel file.

Task Factory Excel Power Refresh Pivot Table Sheets

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):
    SSMS New Credential example
  2. Create a proxy account based on the previously created credential. Select the SSIS Package Integration Services Package subsystem: SSMS New Proxy Account Active to the following subsystems example
  3. Set the job step to run as the proxy account:
    SSMS New Job Step example
  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

Important:  Excel Source is available for SQL versions 2012 and higher
Source Icon Source Description
Task Factory Excel Source Icon The Excel Source connects to an existing Excel file and extracts its data.

File Format

Task Factory Excel Source 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. -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

Task Factory Excel Source 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.
Additional Information:  See the Task Factory Error Row Handling article for more information about this functionality.

Excel Destination

Important:  Excel Destination is available for SQL versions 2012 and higher.
Destination Icon Destination Description
Task Factory Excel Destination Icon The Excel Destination connects to a new or existing Excel file and writes data to it.

File Format

Task Factory Excel Destination 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

Task Factory Excel Destination 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.)
Additional Information:  See the Task Factory Error Row Handling article for more information about this functionality.

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
Additional Information: For more Excel Style information, please refer to this blog post here.