Task Factory Excel

Connection Manager

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.

Excel Power Refresh Task

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


OptionDescription
Excel 2013 FileDefines the path to the Excel file to be refreshed.
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.


Power Query ConnectionsPower Pivot Sheets
Users can select individual connections to be refreshed in the selected Excel file.
Task Factory Excel Power Refresh Data Connections
Users can 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):
    Task Factory New Credential
  2. Create a proxy account based on the previously created credential. Select the SSIS Package Integration Services Package subsystem:
    Task Factory New Proxy Account Active to the following subsystems
  3. Set the job step to run as the proxy account:
    Task Factory New Job Step
  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\
Task Factory Add folder to systemprofile directoryTask Factory Add folder to systemprofile directory

6. Restart the job.

Excel Source

Important:  Excel Source is available for SQL versions 2012 and higher

Excel Source

Source IconSource Description
Task Factory Excel Source IconThe Excel Source allows users to connect to an existing Excel file and extract its data.

File Format

Task Factory Excel Source File Format

OptionDescription
Excel Connection ManagerUsers can create a new or select an existing connection to an Excel worksheet. 
Choose WorksheetFor Excel files that use many worksheets, users can choose which one to extract data from.
First Row Has Header NamesThis 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 SettingsUsers can choose from the following:

  • Region - Use the Row and Column values below to define the data range.
  • Named Range - Users can select from named ranges created in an Excel file. When Named Range is selected, a dropdown box appears. Users can then choose from the available named ranges within the document.
RowsDetermines 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.
ColumnsDetermines the number of columns in the output. -denotes using all available columns.
Output Format
  • Formatted - Users should 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 PreviewSelecting this option enables and displays the preview based on the component's configuration.

Columns

Task Factory Excel Source Columns

OptionDescription
Code PageIdentifies character encoding.
Column Name In FileIdentifies the name of the column coming from the Excel file.
 DataTypeAll datatypes default to DT_WSTR but can be edited for output to be integers, boolean, strings, etc.
Excel Column IndexDefines the numeric order of the column.
Include In OutputSelected by default, users can determine whether or not the column is included in the output.
LengthDefines the number of characters for string and wstring datatypes.
 Output Column NameDefines the name of the column in the output. Column names can be edited here.
Precision Defines the number of digits for numeric datatypes.
ScaleDefines the number of digits after the decimal for numeric datatypes.
Refresh Excel ColumnsRefreshes 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.

Excel Destination

Destination IconDestination Description
Task Factory Excel Destination IconThe Excel Destination allows users to connect to a new or existing Excel file and write data to it.

File Format

Task Factory Excel Destination File Format

OptionDescription
Excel Connection ManagerUsers can create a new or select an existing connection to an Excel worksheet. For more information, see the Excel Connection Manager.
Choose WorksheetExcel users can create many worksheets within the same file. Users can select which worksheet to load data to.
Existing Sheet DataUsers can 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 StartDetermines the row number to begin writing.
Column StartDetermines the column number to begin writing.
Create TableSelect this option to add table formatting.
Table NameUsers can define the name of the table.
Create Table Auto FiltersSelect to create auto filters.

Columns

Task Factory Excel Destination Columns

Option Description
Excel Column MappingMaps the column to the excel file's cell.
Excel ColumnsUsers can edit or rename a column that is written to the Excel file.
Include In SpreadsheetThis option is selected by default. Clear this option to prevent the column from being output to the Excel file.
StyleColumns can be formatted using styles within Excel. Please see the Excel Style page for more information.
Refresh Excel ColumnsRefreshes 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 CodeDescription
GeneralGeneral number format.
#Digit placeholder that represents optional digits and does not display extra zeros.
0Digit 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.
EScientific 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:

SymbolDescription
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

CodeDescriptionExample
0Digit 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,,
5050
.05.00
5005001.50.00
50005,00055.00.01
5550055,5005655.50.06
555500555,500556555.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
5550055.50 K55.5 K.06 M.0555 M
55550005555.00 K5,555.0 K5.56 M5.5550 M
55550000005555000 K5,555,000.0 K5555.00 M5,555.0000 M

Temperatures

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

Display Leading Zeros

  • Users can add 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

  • Add 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 users 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

FormatDescriptionExample (January 1, 2018)
mMonth number without a leading zero1
mmMonth number with a leading zero01
mmmMonth name in short formJan
mmmmMonth name in full formJanuary
mmmmmMonth as the first letterJ
dDay number without a leading zero1
ddDay number with a leading zero01
dddDay of the week in short formMon
ddddDay of the week in full formMonday
yyLast 2 digits of the year18
yyyyAll 4 digits of the year2018

Custom Time

FormatDescriptionExample (January 1, 2018)
hHours without a leading zero0-23
hhHours with a leading zero00-23
mMinutes without a leading zero0-59
mmMinutes with a leading zero00-59
sSeconds without a leading zero0-59
ssSeconds with a leading zero00-59
AM/PMPeriods of the dayAM or PM

Additional Information: For more Excel Style information, please refer to this blog post here.