SAP Integrator vs. SSIS

general discussion
1

Hi All,

What will be the major differences between using SAP Integrator (BW) to bring in SAP data in comparison to using SSIS batch jobs to bring the data direct to SQL Server DB from the backend of SAP?

I know there will be performance difference but how significant will it be?

Thanks alot everyone

Jamesy121 (BOB member since 2009-05-04)

2

SSIS is a lot quicker…

jemstar :ireland: (BOB member since 2006-03-30)

3

SSIS allows for configurations that can be stored in a DB, XML file using which you can dynamically change the connection / execution parameters without modifying the finished source code. You can store SSIS packages in SQL Server repository or file system repository and schedule it via SQL job scheduler. SSIS supports a very powerful feature called “expressions” using which you can change parameters of data flow / control flow tasks at run time. If your company is using the Enterprise Edition of SQL Server, SSIS is free (not sure about Standard Edition). We use SSIS for our ETL layer and BOXI for our reporting layer and are very happy thus far with SSIS.

I have no intimate knowledge of Data Integrator to make comments on it except that it was quite pricey when we invited SAP over for a demo. Hope this helps!

JSanthanam :india: (BOB member since 2003-12-17)

4

SAP Data Services (formerly BusinessObjects Data Integrator) does all of those things, and more.

SSIS is catching up, but still isn’t as productive or feature-rich as Data Services. You might pay for Data Services, but connectivity to SAP along with all of the other features will pay for itself in developer time.

dnewton :us: (BOB member since 2004-01-30)

5

Check the Data Integrator/Services section - there is a topic there on BODI/BODS vs other ETL tools.

I have used SSIS for quite a while along with other ETL tools but in my opinion, it doesn’t come close to Data Services by a long shot.

A short summary:

  • Data Services is far easier to use, resulting is much higher productivity
  • Code/Object replication and re-use is far superior in Data Services
  • SSIS SCD2 “generator” is not even close to the ease of TC/HP/KG transforms in Data Services
  • Data Services has far superior Data Quality support - parsing of address data, name matching, using custom dictionaries, etc
  • Data Services is not tied to any specific database platform or vendor

We once did a study to replace Data Integrator 11.5 with SSIS 2005 from a cost saving point of view. As a “POC”, we had one team using BODI 11.5 to further develop the EDW and another team using SSIS 2005 to develop new data streams and specific data marts.

After a few months, it became very clear that it just took far longer to develop new data streams and data marts in SSIS and there were far more complaints from the developers on SSIS bugs or just very time consuming or user unfriendly features. Mind you, we had boosted our “SSIS team” with some experienced SSIS consultants, so it was not due to a lack of expertise.

The “BODI team” had half the resources and at least the same amount of work to do - but in the same amount of time, the BODI team had finished all its objectives while the SSIS team was still struggling.

One of the most suprising things was the inferior XML support of SSIS when compared to Data Integrator 11.5 - which was quite outdated, as Data Integrator 11.7.x was already the most current version at the time.

We really expected SSIS to be even better at processing XML - seeing how Microsoft was one of the biggest supporters of XML … and everything from IIS to SQL Server to Office was supporting/using XML.

But the old Data Integrator 11.5 was just far superior in unnesting large, complex XML structures into relational structures to be loaded into our staging databases. Also Data Integrator had far better support to consume data from our SAP systems. At that point, it was decided that replacing Data Integrator with SSIS was just not a viable option. The amount of rework required would have been far too costly - even when taking the reduced license costs into account.

Now things have improved with SSIS 2008 but Data Services 12.2 is lightyears a head of the old Data Integrator 11.5 we were using back then.

At the start of my current project, the use of SSIS 2008 was considered but after demonstrating the Data Services 12.2 features, speed of development and Data Quality features, the customer decided to purchase Data Services despite owning and using SSIS already. The cost savings in time and resources alone were far exceeding the license costs of the full Data Services suite.

ErikR :new_zealand: (BOB member since 2007-01-10)

6

Wow thanks for the detailed and valuable input.

Loving it 8)

Jamesy121 (BOB member since 2009-05-04)

7

can you help me with the comparison between SSIS 2008 and BO DS R3…in terms of a tabular format

JANHAVI (BOB member since 2010-12-03)