I'm new to integration services.
I want to create a centralized reporting system for our customers. Some
customers have up to 1,000 sites and some are expected to grow past 2000
sites. The sites are running POS applications and I want to extract the POS
sales data from these sites. Is it practical to expect that SSIS can handle
the extraction of data from this many sites and load the data into a central
SQL database? The POS sales data at the sites is stored in SqlExpress
databases but the data is also available in XML format.
If it's practical for Integration Services to do this what frequency is it
possible to pull this data?
I realize that the amout of data is relative but just wandering if anyone is
attempting to do this with integration services. If not with integration
services, then what method(s) are available and used to extract data from
this many remote sites?

My thoughts

Technically yes SSIS can do this. Practically though it may not be the best
tool if you want real time. For POS systems have you thought about
replication? Have you also though about Biztalk? SSIS is not really a real
time tool although it can be. If you want batched delivery say ~ every 15
mins or more then sure SSIS becomes more of a player. The issue is that
there will be an overhead to firing up an SSIS package and that overhead for
next to real time may be prohibitive. Replication would still be good
though.

How much data are you talking? what size?

Neither of these is an issue for SSIS to move but it will affect your choice
of delivery if you want real time.

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"LeeH" <LeeH (AT) discussions (DOT) microsoft.com> wrote

Thanks for the reply...
Integration Services "implies" that it can do this (multiple sources)
however nothing ever mentions how many "multiple" is so I was curious...
I don't replication would be an option because the sites are using
SQLExpress and as I understans it, SQLExpress can only be a subscriber and
not a publisher. I don't know anything about BizTalk however, one
restriction is that it can't be too expensive for the sites to implement
(hence SQLExpress).
Ideally, I would like to get the site information every 15 minutes (near
real time). This data would be relatively small.

"Allan Mitchell" wrote:

Yeah you could string multiple sources together in one pipeline sure. Every
15 mins and relatively small loads per batch == SSIS. You would only need
to build 1 package if all the sites are the same idea.

Replication yep, can only be a to Transactional and Merge replication and
POS is suited to Merge.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/03d23219-b3b2-413d-b524-3b21d61e8606.htm



--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"LeeH" <LeeH (AT) discussions (DOT) microsoft.com> wrote

Thanks again Allan.
I'm not sure I understood the "Replication yep, can only be a to
Transactional and Merge replication and POS is suited to Merge." response.
Are you agreeing w/me that replication is NOT an option since the sites are
using SQLExpress and SQLExpress at the site CANNOT replicate to the Corporate
SQL Server?


"Allan Mitchell" wrote:

The link I posted was a link in BOL for using replication for POS. The link
also says Merge is the best suited for this. SQL/E can only be a sub to
Merge.

It is my understanding here that the Corporate Db here would be the Pub and
the Subs would be the tills and therefore the topology works.

You might want to head over to the replication NG and check it out but I
think this is right

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"LeeH" <LeeH (AT) discussions (DOT) microsoft.com> wrote

Ok I'll do that.
If I implement 1 package and string multiple sources together in one
pipeline as you suggest, how does Integration Services handle this? Does it
run multiple threads to accomplish this many connections and data transfers?
BTW - If I need to scale to 5,000 sites do you think that Integration
Services would still be an option?


Do you know of any other resourse I could run this implementation by. This
is an important decision. I would hate to waste valuable time developing in
this direction if it want scale to what I need.

"Allan Mitchell" wrote:

I never said to put all your sites in 1 package. That would very quickly
become unmanageable.

You can build one package and have a configuration set the values for the
SQL Server. This way you can call the same package for all your sites just
passing in the site specific connection info.



--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"LeeH" <LeeH (AT) discussions (DOT) microsoft.com> wrote

Ok but how does Integration Services handle this? Does it run multiple
threads to transfer the data in parallel?
Also - If I need to scale to 5,000 sites do you think that Integration
Services would still be an option?
Do you know of any other resourse I could run this implementation by.
This is an important decision and I would hate to waste valuable time
developing
in this direction if it wont scale to what I need.

"Allan Mitchell" wrote:

Or of course you could put things in a loop and do it that way

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote