?
Solved

Is there an alternative to run "SSIS like" process on remote client server without a full SQL Server install?

Posted on 2010-01-08
10
Medium Priority
?
610 Views
Last Modified: 2013-11-10
We are working on a project that will allow remote clients to pull data from legacy databases into a SQL express table that we will consume via a web service.  There will be some data transformation that needs to take place in the import process.  In the past I have simply used SSIS for this type of task, however, since this is in a client environment that may be duplicated 100 times, the cost of implementing full SQL servers in every remote site is killing us.  I would like to simply install SQL Express, and use some other method of getting the data from the legacy databases into our SQL tables.  Is there any good alternatives? How is PowerShell at doing pivot or sync operations?  Is PowerShell too slow to handle as many as a few thousand records?  Is there a way to perform SSIS functionality in a custom C# application without writing LOTS of code?  I wish there was an SSIS runtime...
0
Comment
Question by:TriadX1
  • 6
  • 4
10 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26212674
in each cliente try to call a SQL Job or Stored Procedure that call the SSIS package that are stored in SSIS Server...
Helped?
regards,
Pedro
0
 
LVL 1

Author Comment

by:TriadX1
ID: 26212889
Thanks Pedro, but the problem is that we have no guarentee that our remote clients will even have a SQL install that has SSIS (ie. SQL Server 2008 Standard or above).  Also, it is our policy not to install our applications on Client Servers.  What we are trying to avoid is purchasing a full SQL server license at each remote site.  What our ideal package would consist of ideally is a) a Windows Web Server 2008 server, b) a web service, c) a SQL Express database(s) and d) a solution to sync data from various legacy datbases to our own proprietay SQL express table.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26212959
I never tried, but you already tried to call a SQL job on the server through the SQL Express clients?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:PedroCGD
ID: 26212970
or call a stored procedure
0
 
LVL 1

Author Comment

by:TriadX1
ID: 26213585
Stored proceedures will work when we are talking to other SQL Server databases, but may not be very usefull when we need to import data from MySQL or Oracle, etc.   I think we are going to have to setup our own SQL server with SQL Standard, but wanted to see if anyone had any better ideas before we take on the cost of doing so.  Thanks!
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26282019
When I told you to use stored procedure is only to call the SSIS package not import and do all the work inside it. You can call a stored procedure remotly in each client and the stored procedure in the server could call the SSIS package... understood!?
regards,
Pedro
0
 
LVL 1

Author Comment

by:TriadX1
ID: 26285437
But my question was to use something other than SSIS, because we don't know if SSIS will be installed at the client end, and it we used SQL Express, SSIS in not included with that...  So it is impossible to call a SSIS package using any method if SSIS is not installed.  What I was looking for was an alternative to SSIS. Sorry for the miscommunication.  
Brad
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26285540
Sorry... but you dont understand what i wrote...

YOU DONT NEED SSIS IN THE CLIENT if you have a stored procedure in the server that call the SSIS package located in the server... what you need it's just call this stored procedute from each client using SQL express!!!

0
 
LVL 1

Accepted Solution

by:
TriadX1 earned 0 total points
ID: 26285651

I guess I am not making myself very clear.  There are no servers with SSIS at our client facility.  Example:  Our client is running MySQL and I need to import that data into a SQL Express database which will also reside on the client network.  In this case, there is no server that would have SSIS on it to execute.

So, what we have decided to do is rather than use SQL Express like I had hoped, we are going to have to install a full SQL Standard so that there IS a server with SSIS on the client that we can use for importing data...  I was hoping to avoid having to license SQL Standard by using SQL Express, but since SSIS is looking to be necessary, we will have to take that cost.  
Thanks for taking the time to try to help us on this issue Pedro, it is much appresiated.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26295761
very good... The SSIS is included with SQL... Install only on the server...
I hope I helped you!
If you dont have more issues related to this subject, just set this question as answered.
Always welcome.
regards,
Pedro
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question