------------------------------------------------------------------------------------------------------------
SSIS Basics Package Creation Video Example SSIS 2012
Task:
Control Flow Tab:Data Flow Task:clicking on it take to "Data Flow" tab [ here specified "Source" system from which Data is pulled and populated to "Destination".]
Control Flow Tab:Send Mail Task:A "precedent" "constraint" is applied - on Job failure to send an email.
Transformation:
Data Flow Tab:
Derived Column Transformation:
Derived Column Name: Full Name Expression:FirstName+" "+Surname
note:
Using Derieved Column Transformation , the value of the column also changed.
------------------------------------------------------------------------------------------------------------
1.Visual Studio:SQL Server Data Tools (versn.2012):BIDS(Business Intelligence Development Studio):Part of Sql Server Installation.
1.To create SSIS Package need , "SQL Server Data Tools" installed ( part of Sql Server 2012).
note1:In previous vers. of Sql Server: "Business Intelligence Development Studio"("BIDS").
note2:Both of them integrated into "Visual Studio" , part of "Sql Server Installation".
2.[i.Project:Solution Explorer:Project.params:Connection Managers] [ii.SSIS Packages:package.dtsx] SSIS Toolbox.
2.To create package you click on "SQL Server Data Tools"
Also when you create Package first you create a Solution for SSIS.
To do that: File->New Project->Integration Services Project
Business Intelligence
Analysis Services
Integration Services
Reporting Services
SQL Server
Other Project Types
notes:
i.when you create a solution , it creates project , in one solution, you can have multiple projects.One can even include one SSRS project.But most solution contains one project.
SSIS Basics
Project.params
Connection Managers
SSIS Packages
Package.dtsx
(vers.2012:Variables(change value during execution of the package),Parameters(deploy in diff. env.(dev,prod) and change value(connection)
ii.Tabs:
Control Flow,Data Flow,Parameters,Event Handler,Package Explorer
Parameters:change connection string to point to Dev. or Prod.
a.Variables - change the values in runtime.
note:Previous vers. only had variables , which one can use to change values inside package
and also outside of package but now it has "Parameters".
b.Control Flow
Data Flow Task ( Name It : Import MyFile)
Send Mail Task
i.Constraint (Precedence Constraint)
Value:Failure
[OLE DB: Destination(check constraints,keep identity,table lock,keep nulls)][Data access mode:Table or view - fast load][Mapping]
5.Configuration:Connection Manager:Expression( dynamically change):Solution Explorer:Project.params
6.New 2012 Vers.:Connection Manager(right click):Parameterize:
In "Connection Manager" , in property: Connection String: the value is hard coded , there is another property "Expression:" that at run time can change the value ( its in 2012 ) , when required to point it to dev.,prod. or test.
To do that: just below solution explorer the proect.param which is outside of project , there create a parameter for the connection string. Then choose the OLEDB connection string and right click and choose "Parameterize" , there put this paratermeter that is newly created.
7.Deploy:Configuration properties:Deployment:Server name:
Righ click on Solution Explorer , choose "Deployment" that deployes the package to the Server. But first check all configuration so right click then choose properties and in the configuration properties set the values.
---------------------------------------------------------------------------------------------
SQL Server Management Studio(SSMS)
[from visual studio:Data Tools:Click on icon SSMS & log on to Database Engine]
-Database -Security -Server Objects
-Replication -Management -Notification Services
-SQL Server Agent
-right click on Job node-New Job-
Run as: SQL Agent Service Account
Use Windows Authentication
{Package:Properties:Security:ProtectionLevel:EncryptSensitiveWithUserKey
Transactions:IsolationLevel:Serializable
ProtectionLevel - for encrypting the package(default: EncryptSensitiveWithUserKey will often cause problems. sensitive information is a database connection string that contains a password in it.The default setting will encrypt this information using the user key of the person who created the package. When another user executes the package it will fail because that user's key will not be able to decrypt the connection string. A good way around this is to change the ProtectionLevel to DontSaveSensitive, meaning you aren't going to put sensitive information in the package so there's no need to worry about encryption.
IsolationLevel - Serializable is the level where read locks are held until a transaction commits or rolls back which provides that no data read can be updated.choose one of the other levels such as ReadCommitted..
TransactionOption
}
Note that the Run as setting is the SQL Agent Service Account. This is the default setting although from a security standpoint it may not be what you want. You can setup a Proxy that allows you to give a particular credential permission to execute an SSIS package from a SQL Server Agent job step.
-Security
Security then Credentials in SSMS Object Explorer and right click to create a new credential as shown below:
Credential name: SSIS Execution Account
...
Navigate to SQL Server Agent then Proxies in SSMS Object Explorer and right click to create a new proxy as shown below:
Proxy name: SSIS Proxy
Credential name:SSIS Execution Account
Active to the following subsystems:
SQL Server Integration Services Package
---
Now when you create or edit a SQL Server Agent job step
-------
Scheduling SSIS packages with SQL Server Agent
-you to execute an SSIS package in a SQL Server Agent job step.
-it allows you to schedule the execution of an SSIS package so that it runs without any user interaction.
---------
SQL Server Import & Export Wizard:
Data source:SQL Native Client Server name: Database:
Destination: Specify Table Copy or Query:
Select Source Tables & views: Edit Mapping
Save & Execute Package: to perform export operation & to
create an SSIS package and save it to SQL Server or file system.
------
OLE DB Source: Data access mode: SQL command: SQL command text: query
---
Deployment utility:DTUTIL.EXE
---
MSDB database - Sql server packages
---
Deploy to package store: File System node:Import package
---
Executing SSIS package:
DTEXEC,DTEXECUI,SQL Server Agent
---
---------------------------------------------------------------------------------------------
Containers:Sequence,For Each Loop,For LoopLoad:Fast Load,Precedence Constraint,Row By Row Insert
Package Configuration:[2005-08]SQL Table or XML file.[2012]:new added :catalog,parameters
MergeJoin:Sorted data in Source query[Order By]:Instead of:[Sort Transformation: Slow in large dataset.]
MDX query:'MDXResult' variable:NewConnection:Sql Execute Task
------------------------------------------------------------------------------------------------------------
Reference:
SQL Server Integration Services (SSIS)
SSIS Package Architecture Overview Basics Video Example SSIS 2
SSIS Interview Questions and Answers Video SSIS 2012
SSIS Package Architecture Overview Basics Video Example SSIS 2
SSIS Interview Questions and Answers Video SSIS 2012
------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment