SSIS - Invoking / Running / Controlling one SSIS package from another

Refer Source: Invoking / Running / Controlling one SSIS package from another

Individual packages: Flat File Source - > Ole DB Destination

To get the Parent Package Variable value: 

Configurations:

i.Create a variable var_dir

ii.Right Click: Choose "Package Configuration" : choose : Parent Package Variable: map to  var_dir

iii.Expression: concatenate var_dir + file name

Note:

i.In Parent Package: a variable is defined like: var_file_dir  c:\data\

where all the text files are there that need to be loaded

The Parent Package: Which is calling these 3 Packages.

i.3 variables are declared for 3 packages which is boolean and value "True"

This is used to: make the individual package "Disabled" in the beginning.

Configuration to disable: Expression below "Disable"

ii.Package first runs a script task and read the variable values containing just directory information.

iii.File Exist task checks if  file are there or not , if there then set the  boolean value "True" , False

, that way enables the packages to run.

Interactive SSIS package

Refer Source:Interactive SSIS package

Script Task:

i. Take user input of  Table Name. Assign it a variable defined.

Execute SQL Task:

i.

DECLARE @TableName VARCHAR(100)

SET @TableName = ?

Select ? = Count (*) FROM INFORMATION_SCHEMA_TABLES  WHERE TABLE_NAME = @TableName

GO

Execute SQL Task:

Parameter Mapping:

Variable Name         Direction   Data Type          Parameter  

User:TableName      Input         VARCHAR        0

User:ValidateName Output       LARGE_INT...   1

Constraint:

Validation done on the variable.

DECLARE @TableName VARCHAR(100)

SET @TableName = ?

DECLARE @Count  INT

DECLARE @SqlString NVARCHAR(1000)

   SET  @SqlString =  '  SELECT   @OutCount = COUNT(*) FROM  ' =@TableName

EXEC  SP_EXECUTESQL 

                             @SqlString

                             , N'@OutCount INT OUTPUT'

                             ,@OutCount = @ Count OUTPUT 

                             Select ? = @Count

SSIS Performance Tuning Tips and Tricks

SQL Server Integration Services SSIS Best Practices

SSIS Best Practices I SSIS Tutorial I MSBI

Informatica vs. SSIS

SSIS - Incremental Data Load

Refer:

Using Temp Tables Across SQL Tasks - SSIS

Refer Source:Incremental Data Load - The SSIS Approach

Destination Connection Manager:

RetainSameConnection  :True

Approach1:

1.Insert,Update

Sequence Container:Data Flow Task

Ole DB Source:          Ole DB Target :

Sort  ( in both)

Merge Join (Left Outer Join)

Conditional Split(Where Clause)

      Insert  Records (  Id  not in Source)

      Update Record( Change record :   where source.name != Target.name)

example:

Select    from source  a left outer join target b on a.id=b.id

update  target b

set  col

from ...

where ...

note: the  update is done 1 rec at a time.delete 1 rec at a time.it is not set based method like

updating all at a time or many records at a time.

2.Delete

TargetDB

LookUP  ( 

OLE DB Command  (  update ... col1=?  )( ?  replaced by parameters set on it).

note:Deleting  the Id  not in staging but in source, in dw its not done,the data is not deleted.

Approach2:SET based approach

Sequence Container

Data Flow Task

Create TEMP Table  (## -  for  Global Temporary Table)

OLEDB Update

---

Data Flow Task:

OLE DB Destination:

    ValidateExternalMetaData:False

note:instead of   "OLE DB Command"  use "OLE DB Destination"

Approach3:Merge &  OLE DB Source & OLE DB Destination.

Sequence Container

Data Flow Task

Create TEMP Table

note:loading the source table  to tempdb of  target server.

MERGE  EMP_TAREGT      AS  T

USING    ##EMP_SOURCE  AS   S

ON (T.ID=S.ID)

WHEN NOT MATCHED BY TARGET

THEN INSERT (ID,NAME,DEPT) VALUES (S.ID,S.NAME,S.DEPT)

WHEN MATCHED AND T.NAME < > S.NAME

OR ISNULL(T.DEPT,'AA') < > ISNULL(S.DEPT,'AA')

THEN UPDATE SET T.NAME=S.NAME

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

note: OLEDB  Source  ,  ValidateExternalMetaData:False ( runtime the table is going to be created.)

SSIS - Encrypt

Refer Source:

Encrypt Your SSIS Package - Learn About Protection Levels