asked
When To Use: SSIS/SQL Agent vs. T-SQL/SQL Agent?
I've been using SQL Agent to run T-SQL jobs that populate a local database -- sort of a mini-data warehouse. By adding linked servers and all that, it's been happily gathering the information I need. SSRS provides the BI for the users, and it's been working well. Lately I've also been exploring SSIS. I understand I can use Agent to run SSIS packages. If T-SQL/Agent does the same thing, however, *when* and *why* would I want to use SSIS packages/Agent? I'm new to SSIS, and am trying to determine whether to switch my future ETL to that application.
Why SSIS? 1. SSIS can help you get data from heterogeneous data sources like XML, EXCEL, Flat files and most of the DBMS systems available now. 2. Reusable code by making things like connections dynamic 3. No coding, just design your logic using drag n drop interface 4. Can do complex actions like incremental data loads, bulk data cleansing etc easily There are a lot of other things... Google!! :)
SQL Agent is just a scheduler. You can schedule DTS, TSQL, OS Commands, SSIS, etc. I think what you really are asking is why move from coding everything in TSQL, SPROCS, etc to SSIS. The reason would be to take advantage of the features and functionality of SSIS instead of linked servers, temp tables, etc that you have to with plan ole TSQL and linked servers. If you are building complex ETL processes SSIS can be your friend by enabling you to build robust reusable packages. Data conversion, data flows, exports to excel, flat files, etc are a breeze. Crap, my response is being cut short by a production issue. I will respond more in a quick bit.
