SSIS or Sql Server Integration Services can used in current requirements, to perform opertions like Extraction Transformation Load (ETL). If there is requirement to produce flat files, excel files, Comma Separated Files or any other types of requirements like movement of data into database or from database.
SSIS code is saved in .dtsx extension and requires DTExec runtime to execute. Dtexec in turn requires .net framework.
Inorder to Start with SSIS, you need to have Business Intelligence Studio which comes with SQL server and during installation please check install BI option during installation.
SSIS package has two main components 1) Control Flow 2) Data Flow component.

Storing of Images and other unstructured data is quite common activity and most developers has done this task in their own way like by saving Data File in some file server and storing the address in database. Some might have saved the files in DB only by storing the Unstructured File in ByTe[] array and saving in BLOB or VARBINARY data type.

SQL Server 2008 R2 has introduced new way of storing unstructured Data files in BLOB or VARBINARY(MAX) and getting it managed by NT based file system. This new method overcomes all the overheads caused by saving Unstructured data in DB. Reading and Writing data from FileStream is same as using VarBinary or Blob type columns.

In order to Perform ETL using SSIS Data Flow Task or DFT plays an important role. DFT is responsible for Movement of Data from Source-to-Stage or from Stage-to-Mart. DFT has three main components

  • a) Source
  • b) Transformation
  • c) Destination

Below image show an empty Data Flow Task.

  • 1) Depending upon source from where we need to Extract the data, The source is selected from different Sources e.g. Excel, Flat File, OLE DB, RAW file, XML etc.
  • 2) A suitable Transformation is selected
  • 3) Finally Depending upon requirement The suitable Destination is selected.

Every software Application has a business logic associated with it.Change in business logic is regular and inevitable activity. But Some times change is like a knock on Devil’s door and results could be catastrophic. So there are discussions about placement of business logic in Classes(Layer) of Web/Windows applications or in SQL/PL-SQL in form of Stored Procedures/User Defined functions. Both Approaches have their own Pros and Cons. We will discuss both of them one by one.

  • Business logic at SQL(PL\SQL) LevelIn this approach the Business logic is kept mainly at SQL level in form of Stored Procedures or User Defined Functions.
    • Pros:
      • The process to change business logic is easy as we have to have the logic in Stored Procedure or Functions, it doesn’t require change in Business Logic implemented in our application
    • Cons:
      • Implementation of Business logic is little difficult task as compared to handle in .net/Java etc.
  • Business logic at Business Layer of ApplicationIn this approach the business logic is kept at Application level in form of Class or APIs or DLLs
      • Pros:

    It easy to code business logic as compared to SQL. Manipulation is always better at JAVA/.net end.

    • Cons:
      • The process to accommodate change is difficult as compared to changes at SQL level.
      • Change can induce errors at different levels in application.