SSIS stands for SQL Server Integration Services. It is:
- a platform for building enterprise-level data integration and data transformations solutions.
- built to assist with database administration, and facilitate business intelligence.
Data integration\ transformations -Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
- Data can be migrated between SQL Server tables, Excel files, flat files and many more
- Data can be transferred at table or query level. This gives DBA the ability to either aggregate the data or transform the data as it is migrated.
Data Integration tools like SSIS are mainly used to perform Extract, Transform, and Load (ETL) operations. From a high level, BI projects usually involve an end point of reporting. Often times as developers we are used to transactional report writing such as the details of a PO but BI can get into very broad reports that cover product sales trends over decades and deal with hundreds of millions of records. The way we design databases for applications is not ideal for this kind of reporting so other tools and technologies were invented and are used in the BI space. These are things like Cubes which you often hear called OLAP cubes. OLAP cubes usually originate from a data warehouse which is nothing more than another database – but typical warehouses contain data that came from more than one, and often dozens of other application databases. Your inventory app, purchasing app, HR app and a whole bunch of others all contain bits and pieces of data that create a complete picture of the business, a BI architect will use something like SSIS to pull the data from all these systems, massage it and store it in the data warehouse which is designed with a different kind of design better for reporting. Once it is in the warehouse he will use Analysis services to create cubes on that data and something like Reporting Services to show you reports over that data.