|
Category |
Feature |
MS Integration Services |
Informatica PowerCenter |
|
Data Sources |
Fixed File |
Yes |
Yes |
|
Delimited File |
Yes |
Yes |
|
|
XML |
Yes |
Yes |
|
|
Web service |
Yes |
Yes |
|
|
OLEDB/ODBC |
Yes |
Yes |
|
|
Message Bus |
MSMQ |
MSMQ, TIBCO, Websphere, Solace |
|
|
Other |
SAP, Hadoop, ... |
SAP, Hadoop, ... |
|
|
Change Data Capture |
SQL Server, Oracle, other options are available from third party vendors |
SQL Server, DB2, Oracle, Teradata, ... |
|
|
Conversion on extract |
Yes |
Yes |
|
|
Transformations |
Change and History Management |
Slowly Changing Dimension |
No SCD transformation. Use the SCD Wizard |
|
Contact/Address Cleansing |
No |
Data Cleanse and Match Option |
|
|
Fuzzy Grouping |
Fuzzy Grouping |
No |
|
|
Fuzzy Lookup |
Fuzzy Lookup |
No |
|
|
Language Processing (search) |
Term Extraction |
No |
|
|
Language Processing (lookup) |
Term Lookup |
No |
|
|
Data Mining |
Data Mining Query |
No |
|
|
Data Quality |
DQS Cleansing |
Data Quality, Data Services |
|
|
Character Map |
Character Map |
No (Use expressions) |
|
|
Copy Column |
Copy Column |
Expression |
|
|
Derived Column |
Derived Column |
Expression |
|
|
Export Column |
Export Column |
No (Use Flat File Target) |
|
|
Import Column |
Import Column |
No (Use Flat File Source) |
|
|
Script |
Script |
Java |
|
|
OLEDB/ODBC |
OLEDB Command |
Use Data Source |
|
|
Aggregate |
Aggregate (7 functions) |
Aggregator (12 functions) |
|
|
Sort |
Sort |
Sorter |
|
|
Percentage Sampling |
Percentage Sampling |
No |
|
|
Row Sampling |
Row Sampling |
Rank |
|
|
Pivot |
Pivot |
No |
|
|
Unpivot |
Unpivot |
Normalizer |
|
|
Conditional Split |
Conditional Split |
Router |
|
|
Multicast |
Multicast |
Router |
|
|
Union |
Union All |
Union |
|
|
Merge |
Merge |
Use Update Strategy |
|
|
Join |
Merge Join |
Joiner |
|
|
Lookup |
Lookup |
Lookup |
|
|
Cache |
Cache |
Configured in the Lookup |
|
|
Audit |
Audit |
No |
|
|
Row Count |
Row Count |
No (use a variable) |
|
|
Custom |
Script Task / Script Component |
Custom |
|
|
Data Masking |
No |
Data Masking |
|
|
External Process |
Execute Process Task |
External Procedure |
|
|
Filter |
Use Conditional Split |
Filter |
|
|
HTTP |
HTTP Connection Manager |
HTTP |
|
|
Key generation |
No (use a script component) |
Sequence Generator |
|
|
Execute SQL |
Execute SQL Task |
SQL |
|
|
Execute SQL Procedure |
Execute SQL Task |
Stored Procedure |
|
|
Transaction Control |
Configured by properties |
Transaction Control |
|
|
Unstructured Data |
Various Data Sources |
Unstructured Data |
|
|
XML |
XSLT, XML Connection Wizard |
XSLT, XML Source Qualifier, XML Parser |
|
|
Code and Extensibility |
Expression Language |
SSIS Expression Language |
Informatica Expression Language |
|
User Defined Functions |
No |
Yes (Informatica Expression Language) |
|
|
Execute SQL Server Procs |
Yes |
Yes |
|
|
Code Extensibility |
Script Tasks / Components (C# or VB) |
Custom functions (in C), Java transformations |
|
|
Other External Procs |
Yes |
Yes |
|
|
Data Quality |
Data quality toolset |
Data Quality Services |
Data Quality, Data Services |
|
Row level validation |
Yes |
Yes |
|
|
Fuzzy matching |
Yes |
Yes |
|
|
Developer Productivity |
Development Environment |
Visual Studio Data Tools |
PowerCenter Designer |
|
Source Repository |
External SC |
Informatica Repository / External SC |
|
|
Debugger |
Step, Breakpoint, Data Viewer |
Step, Breakpoint, Data Viewer |
|
|
Modularity |
Packages, Tasks, Components |
Batches, Worklets, Transformations, Mappings |
|
|
Reuse |
Script Tasks, Script Components |
Worklets, Mapplets |
|
|
Strong typing |
Yes |
Yes |
|
|
Metadata management |
No |
Yes |
|
|
Lineage and impact analysis |
In package only |
Yes |
|
|
Performance, Scalability, Resilience |
Partitioning Support |
Yes |
Yes |
|
Aggregation Strategies |
Presorted, Cached |
Presort, Cached with configurable cache size |
|
|
Lookup |
Full, Partial or No Cache |
Static, Dynamic, Shared or Persistent |
|
|
In Memory Transformation |
Yes |
Yes |
|
|
Transactions |
Commit/Rollback at Task level |
Commit/Rollback at Row or Transformation level |
|
|
DR Failover |
No |
Yes |
|
|
Scale-out / Load balancing |
No |
Yes |
|
|
Checkpoints/Restartability |
Yes |
Yes |
|
|
Autonomous transactions |
Yes |
Yes |
|
|
Instrumentation and logging |
Logging |
Yes |
Yes |
|
Error handling |
Yes |
Yes |
|
|
Security |
Securable objects |
Package |
Batch, Worklet, Transformation, Mapping |
|
Scheduling |
|
Any scheduler |
Informatica's scheduler or any other scheduler |
|
Configuration and deployment |
Deployment unit |
Package |
Worklet |
|
Main elements of design-time logic |
Controlflow, Dataflow, MDM |
Workflow, Worklets, Data Services |
|
|
Configuration |
Yes (variables, connections) |
Yes (variables, sessions) |
|
|
Runtime configuration |
Parameters and metadata only |
Parameters and metadata only |
|
|
|
|
|
|
|
Aggregate Functions |
Average (Mean) |
AVERAGE |
AVG |
|
Count Rows |
COUNT |
COUNT(*) |
|
|
Count non-null values |
COUNT x |
COUNT(x) |
|
|
Count Distinct values |
COUNT DISTINCT |
|
|
|
First value |
|
FIRST |
|
|
Last value |
|
LAST |
|
|
Minimum value |
MIN |
MIN |
|
|
Maximum value |
MAX |
MAX |
|
|
Average (Median) |
|
MEDIAN |
|
|
Percentile |
|
PERCENTILE |
|
|
Standard Deviation |
|
STDDEV |
|
|
Sum |
SUM |
SUM |
|
|
Variance |
|
VARIANCE |
|
|
String Functions |
Character |
|
CHR |
|
Character code |
CODEPOINT |
CHRCODE, ASCII |
|
|
String concatenation |
+ |
CONCAT |
|
|
Convert to Title case |
|
INITCAP |
|
|
Find position of a substring |
FINDSTRING |
INSTR |
|
|
Position in a list of strings |
|
INDEXOF |
|
|
Left substring |
LEFT |
|
|
|
String length |
LEN |
LENGTH |
|
|
Convert to lower case |
LOWER |
LOWER |
|
|
Left padding |
|
LPAD |
|
|
Left trim |
LTRIM |
LTRIM |
|
|
Metaphone |
|
METAPHONE |
|
|
Search and replace |
REPLACE |
REPLACESTR, REPLACECHR |
|
|
Regex substring |
|
REG_EXTRACT |
|
|
Regex matching |
|
REG_MATCH |
|
|
Regex search and replace |
|
REG_REPLACE |
|
|
Right substring |
RIGHT |
|
|
|
Repeat a string value |
REPLICATE |
|
|
|
Right padding |
|
RPAD |
|
|
Right trim |
RTRIM |
RTRIM |
|
|
Soundex |
|
SOUNDEX |
|
|
Stuff String |
|
|
|
|
Substring |
SUBSTRING |
SUBSTR |
|
|
Trim |
TRIM |
|
|
|
Convert to upper case |
UPPER |
UPPER |
|
|
Reverse string |
REVERSE |
REVERSE |
|
|
Conversion Functions |
Type conversion |
(Type) |
TO_BIGINT, TO_CHAR, TO_DATE, TO_DECIMAL, TO_FLOAT, TO_INTEGER |
|
Parse a string |
TOKEN |
|
|
|
Count parts of a string |
TOKENCOUNT |
|
|
|
Convert number to hex string |
HEX |
Use CONVERT_BASE |
|
|
Convert number to string |
Use Type |
Use TO_CHAR |
|
|
Other Functions |
Choose a value from a list |
|
CHOOSE (String only) |
|
Largest value |
|
GREATEST |
|
|
Smallest value |
|
LEAST |
|
|
In a list |
|
IN |
|
|
Is a date |
|
IS_DATE |
|
|
Is a number |
|
IS_NUMBER |
|
|
Is spaces |
|
IS_SPACES |
|
|
Is null (boolean) |
ISNULL |
ISNULL |
|
|
Substitute a value for a null |
REPLACENULL |
Use DECODE |
|
|
Null if equal |
|
Use DECODE |
|
|
Simple CASE |
|
DECODE |
|
|
Complex CASE |
|
|
|
|
Immediate IF |
?: |
IIF |
|
|
Lookup |
|
LOOKUP (Deprecated) |
|
|
Null |
NULL |
NULL |
|
|
Boolean True value |
TRUE |
TRUE |
|
|
Boolean False value |
FALSE |
FALSE |
|
|
Date Functions |
Data addition |
DATEADD |
ADD_TO_DATE |
|
Date comparison |
< = > |
DATE_COMPARE |
|
|
Date subtraction |
DATEDIFF |
DATE_DIFF |
|
|
Name element of a date |
|
|
|
|
Numeric element of a date |
DATEPART |
GET_DATE_PART |
|
|
Day number |
DAY |
|
|
|
UTC system datetime |
GETUTCDATE |
|
|
|
Last day of month |
|
LAST_DAY |
|
|
System datetime |
GETDATE |
SYSTIMESTAMP |
|
|
Make datetime |
|
MAKE_DATE_TIME |
|
|
Round (date) |
|
ROUND |
|
|
Truncation (date) |
DT* |
TRUNC |
|
|
Set part of a date |
|
SET_DATE_PART |
|
|
Month number |
MONTH |
Use GET_DATE_PART |
|
|
Year number |
YEAR |
Use GET_DATE_PART |
|
|
Encryption Functions |
Decrypt by pass phrase |
|
AES_DECRYPT (AES) |
|
Encrypt by pass phrase |
|
AES_ENCRYPT (AES) |
|
|
Compress |
|
COMPRESS |
|
|
Binary checksum |
|
CRC32 |
|
|
Decode Base 64 |
|
DEC_BASE64 |
|
|
Decompress |
|
DECOMPRESS |
|
|
Encode Base 64 |
|
ENC_BASE64 |
|
|
Hash |
|
MD5 (MD5 only) |
|
|
Numeric Functions |
Absolute value |
ABS |
ABS |
|
Next integer >= x |
CEILING |
CEIL |
|
|
Base conversion |
|
CONVERT_BASE |
|
|
Running total |
|
CUME |
|
|
Exponent (natural log) |
EXP |
EXP |
|
|
Previous integer <= x |
FLOOR |
FLOOR |
|
|
Natural Log |
LN |
LN |
|
|
Log Base 10 |
LOG (Base 10) |
LOG (Base N) |
|
|
Modulo |
% |
MOD |
|
|
Moving Average |
|
MOVINGAVG |
|
|
Moving Sum |
|
MOVINGSUM |
|
|
Power |
POWER |
POWER |
|
|
Random number |
|
RAND |
|
|
Truncation |
DT_I* |
TRUNC |
|
|
Rounding |
ROUND |
ROUND |
|
|
Sign |
SIGN |
SIGN |
|
|
Square Root |
SQRT |
SQRT |
|
|
Cosine |
|
COS |
|
|
Cotangent |
|
|
|
|
Hyperbolic Cosine |
|
COSH |
|
|
Sine |
|
SIN |
|
|
Hyperbolic Sine |
|
SINH |
|
|
Tangent |
|
TAN |
|
|
Hyperbolic Tangent |
|
TANH |
|
|
Future Value (financial) |
|
FV |
|
|
Number of Periods (financial) |
|
NPER |
|
|
Payment value (financial) |
|
PMT |
|
|
Present value (financial) |
|
PV |
|
|
Interest rate (financial) |
|
RATE |
|
|
Square |
SQUARE |
|