To homepage To homepage
Solved

Does anyone have experience switching from ADO.NET to SSIS?

B
Rank
Badge

We would like to possibly switch from ADO.NET to SSIS.  At the moment, both the TX application (ODX is not being used) and the SQL server are on the same box using ADO.NET.  We want to split off TX to its own server and use SSIS instead of ADO.NET.  Has anybody done this and can share the high level steps?  Or even how you think it might be best accomplished?  Specifically, once the application server is up and running with all the pertinent software, how would one make this transition?

icon

Best answer by Wim 12 July 2022, 18:22

View original

5 replies

JTreadwell
Rank
Userlevel 3
Badge +5

Hi Brandon, thanks for reaching out on this forum. 

If each TimeXtender target database is on the same SQL Server Instance the recommended approach is using Direct Read-Matching Server. This will have a significant performance gain over ADO or SSIS and does not require TimeXtender to be installed on the same server. 

Will this approach work for you? 

-Joseph Treadwell
B
Rank
Badge

Joseph, hello.  That setting may work.  Currently that feature is disabled.  I planned on bringing that setting to their attention as I had read about it.  At the moment, it appears the main concern is throughput after the server split.  This direct read - matching server is not a setting on the business unit database.  All the databases, including the BU, are on the same server.  Does setting the Direct Read - Matching Server on the data warehouse data connections include the data read from the business unit database as well?  Not into the BU database, but from...

W
Rank
Badge

Hi Brandon we have been using Direct Read since it was first introduced and that's exactly how it works.

From BU to DW data is transferred directly by the SQL server via a stored procedure that does INSERT INTO (your dw table) FROM (your stage table), no intervention needed from TX.

Where ADO is still needed is from Source to BU. But that's only once via the server where TX resides. All the "heavy lifting" afterwards is done by the SQL server.

 

B
Rank
Badge

I think I follow you here, Calmco Support, and I appreciate the response.  I am not familiar (yet) with many of these technical ins and outs, so your comment brings a question to mind.  Do you think there would be much difference in performance between using ADO.NET to populate the BU database on the same server as the TX application, versus using ADO.NET to populate that same BU database but with TX installed on its own server?   

JTreadwell
Rank
Userlevel 3
Badge +5

Hi Brandon, No. I believe the performance difference you would see in your example would be negligible. 

-Joseph Treadwell

Reply


Terms and ConditionsCookie settings