Data Transfer From SQL Server to CRM 2011 using CozyRoc SSIS+

1)Open Sql Server Data Tools
2)Create New Integration Services Project
3)Right Click Connection Manager -> Select New Connection Manager
    Select OLEDB Type
    Select Exiting Data Connection or Create new connection
    Rename 'DNS_DB.conmgr' (DNS sql database connection)

   

Deploying SSIS Packages in SQL Server 2008/2012

1) Create the SSIS Package and Execute it.

2) After executing the solution, the deployment utility is created in the default location if the build is completed successfully. Navigate to the (default location) deployment folder, C:\SSIS\Packages\Import\ExcelFile\bin\Deployment in our case and we will find the deployment files i.e. the integration services project deployment file (ExcelFile.ispac).

3) Copy the Deployment file(local machine)  to the SQL Server Server (Target Server).

Control Flow, Data Flow Task, Precedence Constraints in SSIS

Control Flow
Control Flow contains  a group of tasks to achieve start to end. Without having a Control Flow, no package on SSIS can be made. It also determines under what conditions they're executed.

Data Flow Task
A task where data flows (what ETL does). A Data Flow Task has to be a part of a Control Flow. One control flow can have a number of Data Flow Tasks in it. Each Data Flow Task has to have a Source, may or may not have a Transform/Destination. But at least a transform or Destination followed by the Source. Source is used to extract the data, Transform is used to transform the data while Destination is used to Load the data to a destination.

Precedence Constraints 
Precedence Constraints link the individual executables together and determine how the workflow moves from one executable. The precedence constraints are the green, red and blue arrows (both solid and dotted) that connect the tasks and container to each other. Fig 1.

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS)  -  to get the data from various sources and put the data to tables or create Cubes for data warehouse, used for ETL.

SSIS is a visual tool with drag and drop feature, which enables us to create SSIS, packages to perform ETL in a very short amount of time. 

Business Intelligence Development Studio (BIDS) is the only way where you develop and test the SSIS package. SSIS package is nothing but a complex XML file.

Start All Programs ->  Microsoft SQL Server 2008 R2 -> SQL Server Business Intelligence Development Studio.

SSIS Package Protection Level

Each SSIS component designates that an attribute is sensitive by including Sensitive="1" in the package XML.
An OLE DB Connection Manager specifies that the database password is a sensitive attribute as follows:
<DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>

An DYNAMICS-CRM Connection Manager specifies that the database password is a sensitive attribute as follows:
<ServerPassword Sensitive="1"></ServerPassword>