SQL Server Integration Services

Now a days, SQL Server Integration Services (SSIS) is a powerful data transformation engine that ships with SQL Server because it's well suited for file transfers, data transfers, database maintenance, and various business processes if you are looking to automate. It's user friendly graphical tools and comes with lots of features to full-fill your business requirements.

In simple words, Microsoft development team built SQL Server Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data in a very beautiful manner. 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.

SSIS is the most popular ETL tool. Extract-Transform-Load (ETL) is a type of data integration where data is read from the source, transformed using predefined logic and then loaded into the target in some other form.
SSIS is a great platform to load your data from the various sources and load it into a well defined destination sources. SSIS provides pretty much all the tools that we need to get the job done but sometimes it’s not always easy to know what tools to use and how to use them.
Another thing is that SSIS can extract, transform and consolidate data from multiple relational databases as well as sources such as XML data files and flat files, then load the processed information into an enterprise data warehouse or other target systems. 



As we know that Data warehouses are quickly growing in popularity as a way to manage the “hybrid cloud” situation that many companies now have to deal with as their data sources increasingly are located both in the Cloud and on-premises. So, to maintain their data on the both level, SSIS plays a very important role there. A real data warehouse, as opposed to cubes with disparately organized data, is needed to consolidate all of the islands of information into a central business user friendly repository to create the foundation for modern BI to take place.
ETL (Extract, transfer and load) processes can involve considerable complexity, and significant operational problems can occur with improperly designed ETL systems. This process covers the following steps-
  1. Build reference data and extract from heterogeneous  sources
  2. Validate and transform (clean, apply business rules, check for data integrity, create aggregates or dis-aggregates)
  3. Stage (load into staging tables, if used) and publish data (to target tables)
  4. Data or files archiving

SSIS - SQL Server Stops Loading Assembly
We have created some SSIS package in SQL Server 2014 within Visual Studio 2013 for Business Intelligence. Everything was working fine after deployment them on the Azure Server. Due to some business requirements, our DBA took the back-up of the SSISDB and other data components and restore them another server having the same configurations.
SSIS- Standard Reusable Templates
SSIS is a well-defined ETL platform to load the raw data from the various OLTP or OLAP sources into an established destination. SSIS provides pretty much all the tools that system need to get the job done but sometimes it’s not always easy to know what tools to use and how to use them. To avoid the different approaches, Standard Reusable Templates are the best way to follow the same standard to accomplish the development jobs.

SSIS- The Execute SQL Task is the one of the most important part of SQL Server Integration Services in the Control Flow window because if some information need to pass or get from the database in the package then this control flow item comes into the process. It runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. 
Another thing is that you can use TRUNCATE, SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters within this control.

SSIS - Use Variable in Execute SQL Task
We know that Execute SQL Task is an extra ordinary component in Integration Services. We can use one or more variables in Execute SQL Task to pull the information from the database.  To pull the values from database, we can use the SQL Statement or stored procedure with the parameters. To understand this functionality and know more at
SSIS - Reading Custom Objects Variables in Script Task 
To understand the object outputs, we are using another most important component of SSIS called Script Task. As we know that custom classes are unknown in the other SSIS components and we can get them through the Script Task because they have all the properties and correct values to handle the custom object variables.

SSIS – Read Object Variable in Foreach Loop Container
We are working on the object variables and understanding their behaviour in the various SSIS Controls. We are using Execute SQL Task to store them into object variable and also read them into Script Task. To understand the object outputs, we are using another most important component of SSIS called Foreach Loop Container.

Fuzzy Lookup Transformation in SSIS
Understanding the Fuzzy Lookup Transformations
Configure Fuzzy Lookup Transformation in SSIS
Fuzzy Lookup Transformation for cleaning dirty data


17 comments:

  1. Syntax:
    Business Intelligence Services

    great design of the blog,nice information about and various business processes if you are looking to automate and SSIS - SQL Server Stops Loading Assembly also

    ReplyDelete
  2. Syntax:
    Business Intelligence Services

    Good Design,Easy way to learn and implement the Microsoft technologies.Great Explanation About SQL Server Integration Services.


    ReplyDelete
  3. Hi Mukesh,

    I have attended an interview and interviewer given me a senario based question the question was create a ssis Package and download the ssrs report in pdf format on your local machine and then place those files on sftp server into a folder and then send a email to the user

    ReplyDelete
  4. is this possible if yes then please share the comments with full example

    regards
    Anand Chaudhary

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Excellent blog I write an article to talk about on my blog SQL server : SQL Server Blog

    ReplyDelete
  7. The blog is really good and tells you a lot about SQL

    ReplyDelete
  8. Nice explanations of Tech Cloud ERP’s Business Intelligence Tool, it's good to know that! A friend of mine has implemented a company which is the best ERP software in Hyderabad right now, she provides cloud based ERP software in Hyderabad, so I hope it goes well for her.
    Best Regards

    ReplyDelete
  9. I am so proud of you and your efforts and work make me realize that anything can be done with patience and sincerity. Well I am here to say that your work has inspired me without a doubt. Here is i want to share about c# training with Free Bundle videos and c sharp training online .


    ReplyDelete
  10. Thanks for sharing, I really appreciate your hardwork and Great Explanation About SQL Server Integration Services., It is useful Tableau Certification Dumps

    ReplyDelete
  11. Thanks for sharing your Experience with us...Keep doing This Good Work. Business Central Implementation ,

    ReplyDelete
  12. By adopting Dynamics 365, you will have applications that cover productivity, the analytical side of your company, your CRM and ERP needs and you will also have the possibility of adding custom extensions to your solution according to the needs of your industry. Dynamics 365 Business Central Features

    ReplyDelete
  13. Microsoft Dynamics 365 Business Ccentral (ERP), Ensure continuity of service with an ERP solution that connects sales, service, finance, distribution and manufacturing operations teams to facilitate the achievement of results. Dynamics 365 supply chain management

    ReplyDelete

Popular Posts