Containers in SSIS:
There are 3 containers in SSIS
1.
Sequence
Containers (Folder)
a.
It is
similar to folder
b. It
groups the control flow tasks and then Sequence them
Parallel Execution will happen
if you don’t put arrow
Sequential Execution will
happen if you put arrow:
2.
ForLoop
container:
a. If Condition
is known at design
time
b. It
is similar to loops in any language.
c. It
will run/repeat the task for FIXED no of times.
Ex:
Insert 10 numbers in a table.
--SQL code:
declare @x int
--Initialization
set @x=1
while (@x<=10)
Begin
--Action
insert into tbl_numbs values (@x)
-- Incr/Decr
Set @x=@x+1
End
go
3.
ForEachLoop
Containers:
·
If Condition is unknown at the design time.
·
It is similar to loop in any language.
·
When we do not know ‘How many times we need
to run the loop’
·
Loop is executed for each Object/File etc
Ex: Get the data from All
flat files (.txt) and load into SQL table
Build and deploy the package:
DEV
Tasks:
1. Create
a SSIS Package/Perform Unit Testing
2. Implement
Logging in SSIS
a. SSIS
Menu->Logging- SQL and windows event
b. Event
Handlers (Customize Logging)
3. Build
and Check-in the code to VSTF/TFS
a. Ispac
file needs to be uploaded
b. Path
of Ispac file: <drive>:\<path>\bin\Development
c. Provide
Deployment Guide
DBA
Tasks
1. Download
all files from VSTF to DWH server.
2. Create
Integration Services Catalogs in SQL Server of DWH
3. Deploy
the package using ispac file
a. Deploy
to SQL Server/Deploy to file System
Note: Implement
Configurations for connection strings
4. Scheduled
a package Using SQL Server Jobs
Check Points in SSIS
·
Check
Points restart the package from the point of failure.
·
When we configure Check Point in a package:
i. Creates
the XML/text file.
ii. Takes
one of the following two actions:
1. Saves
the file if the package failed/stopped
2. Deletes
the file if the entire package runs successfully.
·
SSIS will reference the checkpoint file,
when we re-run the packages during failures.
Note: Check Point cannot start the
package at row level. It can start at task level.
Check Point configurations:
·
Configure check points at Control flow
i. Right
click on control flow designer->Properties
ii. Check
Points:
1. Never: A
checkpoint file is not used
2. Always:
Select this to always restart the package from the checkpoint
3. IfExists:
Select this to restart the package only if the check point file is available.
·
Configure the following at Task Level:
i. FailPackageOnFailure=TRUE
Security to SSIS:
Package Security:
·
Use ProtectionLevel property
to secure SSIS package.
·
SSIS encrypts sensitive information, such as
a password or a connection string with a password
·
ProtectionLevel
property had following options:
i. EncryptSensitiveWithUserKey(default)
ii. DontSaveSensitive
iii. EncryptSensitiveWithPassword(recommended)
iv. EncryptAllWithPassword
v. EncryptAllwithUserKey-
Its same as password
vi. ServerStorage
SSIS Utilities:
·
DTEXEC: It
is used to execute a SSIS package from command prompt or from a SQL Server.
·
DTUTIL:
i. The
utility can copy, move,
delete, or verify the existence of a package.
ii. Cannot be deployed to SSIS catalog
iii. Can be deployed to msdb
Call
SSIS package Using Command Prompt:
1)
File
system:
a. dtexec.exe
/f “D:\Demo1.dtsx”
2)
SQL
server:
a. Dtexec.exec/
sq Demo1/ser kunal
Note: Do not use extension.dtsx when the package in SQL server
About
XP_cmdshell:
·
Xp_cmdshell is extended stored procedure.
·
Dtexec.exe is available at
C:\programfile\Microsoft SQLServer\100\DTS\Binn\dtexec.exe
·
/f- means file system Deployment Package
·
/sq – means SQL Server Deployment Package
SSIS Deployment options:
There are 3 ways to deploy a SSIS package
1) Using
.ispac file/Manifest file
2) Using
Command Line Executable called DTUTIL.exe
3) Using
SSDT-> Right click on Project->Deploy