|
SSIS Excel Source
|
 
|
|
SSIS Excel Source can be used to read excel data using Table mode or Query mode (It supports 64Bit and 32Bit mode without installing any additional driver).
|
Note: If you want to operation with multiple files then use wildcard pattern as below (when you use wildcard pattern in source path then system will treat source path as folder regardless you end with slash). Examples: c:\SSIS\Excel\file123.xlsx (single file). c:\SSIS\Excel\file*.xlsx (all files starting with name file). c:\SSIS\Excel\subfolder\*.xlsx (all files with .xlsx Extension and located under folder subfolder).
SELECT TOP 5 * FROM [Sheet1]
Where [OrderID] < {{User::varOrderID}}
ORDER BY OrderID DESC
| Property Name | Description | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| LoggingMode |
LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.
|
||||||||||||||||||||
| PrefixTimestamp | When you enable this property it will prefix timestamp before Log messages. | ||||||||||||||||||||
| TableName | Sheet name or named range indicator from which you like to read data. You can use special table name indicators like [$first_sheet$] or [$1$], [$last_sheet$] or [$SomeNumber$]. You can also use Regular expression to get data from multiple sheets with matching name. Syntax:[regex-pattern]--regex (e.g. MySheet_\d+--regex), for example to get all rows from Sheet names Sales-01,Sales-02..Sales-12 you can set Table name as Sheet-(\\d+)--regex . For named range you can use NamedRange:NAME_FOR_RANGE | ||||||||||||||||||||
| AccessMode |
AccessMode
|
||||||||||||||||||||
| Query | Query to read data. This option only used when AccessMode=Query | ||||||||||||||||||||
| MaxRows | Maximum number of rows to fetch from source. 0=Unlimited | ||||||||||||||||||||
| FormatAllColumns | Output all columns as per excel formatting | ||||||||||||||||||||
| SkipRows | Total number of rows which needs to be skipped (After header) | ||||||||||||||||||||
| SearchStringForTabSelect | Data to search for Sheet selection (enter static string or regular expression with suffix --regex to search for). To use this property you need to set table name with --regex suffix (e.g. Sales(\w+)--regex) and out of all those Sheets it will select only those sheets for data read which has search string found. To search for string in all sheets you can use (.*)--regex as Table name. You can enter static string to search for (e.g. "SALES ORDERS-2023") or use Regular expression in the search string e.g. "SALES ORDERS-(\d+)--regex". This property is useful when you dont know Sheet name but you have idea what data you want to look for somewhere in all tabs. | ||||||||||||||||||||
| Headerless | Data has no header row to indicate column names | ||||||||||||||||||||
| FormattedColumns | List of column(s) which needs to be formatted according to excel format. Multiple columns must be separated by vertical bar (e.g. BirthDate|Amount|Age) | ||||||||||||||||||||
| RangeStartCell | Range start address for data read/write. Address must be excel cell address (e.g. A1) | ||||||||||||||||||||
| RangeEndCell | Range end address for data read/write. Address must be excel cell address (e.g. A1) | ||||||||||||||||||||
| MetaDataScanMode |
Metadata scan mode controls how data type and length is determined. By default few records scanned to determine datatype/length. Changing ScanMode affects length/datatype accuracy.
|
||||||||||||||||||||
| MetaDataCustomLength | Length for all string column. This option is only valid for MetaDataScanMode=Custom | ||||||||||||||||||||
| MetaDataTreatStringAsAscii | When this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode) |
| Option | Description |
|---|---|
| FormattedColumns |
List of column(s) which needs to be formatted according to excel format. Multiple columns must be separated by vertical bar (e.g. BirthDate|Amount|Age) |
| HeaderLess |
Data has no header row to indicate column names |
| SkipRows |
The total number of rows which needs to be skipped (After header) |
| RangeStartCell |
Range start address for data read/write. Address must be excel cell address (e.g. A1) |
| RangeEndCell |
Range end address for data read/write. Address must be excel cell address (e.g. A1) |
| FormatValues |
Output all columns as per excel formatting |
-- Basic Query - Use of Order By, Alias, Comments
-- This example shows how to write a basic query to read data from excel. It support Comments, WHERE Clause, ORDER BY Clause, WITH Clause to specify options.
select
top 5 RowId, FName as [First Name], LastName as [Last Name], Age, BirthDate,Amount
from [Sheet- 1] where RowId < 6 and Amount is not null order by RowId desc with (FormattedColumns='BirthDate|Amount',RangeStartCell='C3',HeaderLess=false,SkipRows=0)
-- Limit rows (TOP N)
-- This example shows how to return TOP N rows only. When you use TOP clause it first applies WHERE clause and then apply TOP
select
top 10 * from [Sheet-1] where RowId < 6
-- Select, Alias, Column name with space
-- This example shows how to use alias for column names, how to use identifiers with space (Table or column name).
select
RowId, FName as [First Name], LastName as [Last Name] from [Sheet-1] where RowId < 6
-- Formatting Columns
-- This example shows how to output certain columns with formatting defined in excel. Use FormattedColumns='Column1|Column2|...' Syntax in the WITH clause. Separate each column by vertical bar.
select
RowId, FirstName, LastName, BirthDate, Amount from [Sheet-1] Where RowId < 6 with (FormattedColumns='BirthDate|Amount')
-- Query from named range
-- This example shows how to query named range just like a table. Use [NamedRange:YourNamedRange] as table name in the FROM clause.
select * from [NamedRange:MyRange]
-- Order By Clause
-- This example shows how order rows first by CompanyName in Ascending and then SalesAmount Descending order (Highest amount at the top).
select * from [Sheet-1] order by CompanyName asc, SalesAmount desc
-- Group By Clause (Not Supported)
-- WITH Clause Options
-- This example shows how to use various advanced options in the WITH clause.
select
* from [Sheet-1] Where RowId < 6 with (FormattedColumns= 'BirthDate|Amount', HeaderLess=false, SkipRows=0, RangeStartCell= 'A1', RangeEndCell='G500', FormatValues=false)
-- Keyword - IN
-- This example shows how to use IN keyword.
select
* from [Sheet-1] Where Country in ('US','UK','Canada')
-- Keyword - NOT IN
-- This example shows how to use NOT IN keyword.
select
* from [Sheet-1] Where Country not in ('US','UK','Canada')
-- Keyword - IS NULL
-- This example shows check for NULL values.
select
* from [Sheet-1] Where Country is null
-- Keyword - NOT NULL
-- This example shows check for Non-NULL values.
select
* from [Sheet-1] Where Country is not null
-- Keyword - LIKE
-- This example shows how use LIKE keyword for pattern search. Use % as wildcard pattern. Only % is supported.
select
* from [Sheet-1] Where CompanyName like 'Zappy%'
-- Keyword - NOT LIKE
-- This example shows how use NOT LIKE keyword for pattern search. Use % as wildcard pattern. Only % is supported.
select
* from [Sheet-1] Where CompanyName not like 'Zappy%'
-- Keyword - AND/OR
-- This example shows how use simple or nested conditions using AND/OR keyword.
select
* from [Sheet-1] Where (GroupNum= 1 OR GroupNum= 2) and Country IN ('US','BHARAT')
-- Regular Expression - Read Sheet(s) (single or multiple screens)
-- This example shows how use select Sheet(s) by doing pattern search (Regular Expression). Use regex pattern for Table name and suffix with --regex to indicate its a pattern search
select
* from [Sheet-(\d+)--regex] Where (GroupNum= 1 OR GroupNum= 2) and Country IN ('US','BHARAT')
-- Regular Expression - Read Named Range(s) (single or multiple ranges)
-- This example shows how use select data from named range(s) by doing pattern search (Regular Expression). Use regex pattern for Named range name and suffix with --regex to indicate its a pattern search
select
* from [NamedRange:MyRange(\d+)--regex] Where (GroupNum= 1 OR GroupNum= 2) and Country IN ('US','BHARAT')
-- Regular Expression - Dynamic sheets select based on the contact search (from single or multiple sheets)
-- This example shows how select sheet by doing data search inside sheet (Regular Expression Data Search). Use regex pattern for Table name and suffix with --regex to indicate its a pattern search
select
* from [Sheet(.*)--regex] Where (GroupNum= 1 OR GroupNum= 2) and Country IN ('US','BHARAT') WITH (SearchStringForTabSelect='SALES REPORT \d+--regex')
t
h
c
|
SSIS PowerPack v2.9.5 released (Maintenance Release)What’s New In this release we focused on fixing some bugs with Dynamics CRM Source , Dynamics CRM Destination and Reporting Services Task. Version 2.9.6.10621 [Jun 24, 2019] New Features/Improvements NEW: Excel Source – Add option to read from any sheet by number rather than hard coded table name (e.g. $4$ , $5$ … ) Bug fixes […] |
|
Load data into PostgreSQL from Excel file using SSISIntroduction In this post, we will learn How to Load data into PostgreSQL from Excel file using SSIS PostgreSQL Destination. We will use SSIS PowerPack to connect PostgreSQL. This article also covers how to read Excel file in SSIS. SSIS PostgreSQL Destination can be used to perform bulk insert inPostgreSQL Table from any data source using SSIS. […] |
|
ic
How to read excel file in SSIS – Load into SQL ServerIntroduction In this post, we will learn How to read excel file in SSIS – Load into SQL Server. We will use SSIS PowerPack to connect Excel file. This article also covers how to read Excel file in SSIS. SSIS Excel File Source Connector (Advanced Excel Source) can be used to read Excel files without installing any […] |
|
|
SSIS PowerPack v2.9.2 released (Maintenance Release)What’s New In this release we focused on mainly focused on fixing high priority bugs related to Upsert Destination. We also added High DPI Monitor Support with Auto Scale UI option (4K Resolution now supported) Full Notes: https://zappysys.com/onlinehelp/ssis-powerpack/scr/release-notes.htm Version 2.9.2.10321 [Mar 21, 2019] New Features/Improvements NEW: Excel Source – Add Option to read first sheet and […] |
