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?
Solved
Does anyone have experience switching from ADO.NET to SSIS?
Best answer by Wim
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.
+5Hi 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, 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...
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.
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?
+5Hi Brandon, No. I believe the performance difference you would see in your example would be negligible.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
