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. 

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.

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


4 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

Popular Posts