Variables in SSIS
-
Purpose
of a variable is to move data from one
task to another or one package to
another.
-
Dynamic connections
-
Loop the files /folders
Variables in SSIS:
There are 2 types of variables in SSIS
1.
System Variables
a. Pre-Defined
b. Read
Only
c. Stores
Package Execution Related info
d. Presented
in Name
Space: System
e. Scope: Local (Task) or Global (Package)
2.
User Variables
a. User
–Defined
b. Read
and Write
c. Presented
in Name Space: User
d. Scope: Local (Task) or Global (Package)
Syntax
to Access Variables:
NameSpace::VariableName
Ex:
·
System::PackageName
·
User::FilePath
*** Variables and Name Space names are case sensitive in
SSIS
TimeStamp:
@[User::FolderPath]+"\\"+"Sales_"+
GETDATE() -- Error received as we need
to convert the getdate to string.
To
write this: E:\MSBI_VIMP\Test\Sales_20200817
Write like this: @[User::FolderPath]+"\\"+"Sales_"+ REPLACE( SUBSTRING( (DT_WSTR,50) GETDATE(), 1, 10),"-","")
Logging:
Customized
Logging: Success
·
Here we need to create a table for log in
our destination DB, see script below.
·
Create a variable RowCnt in package to get
the no. of rows loaded.
·
Add Execute SQL Task-> Add sql command as
Insert into SSIS_Logs values (?,Getdate(),?,'Success.Package executed
successfully.')
·
Add parameter in Parameter mapping. Here, we
are passing the parameter 0 (package name) and 1 (Row Count) in above Insert
Query in question mark (?).
Script:
Use TableName_Stage
go
CREATE TABLE SSIS_Logs
(
ID int primary key identity(1,1),
PkgName Varchar(100) NOT NULL,
PkgExecTime datetime NOT NULL,
Row_Count int NOT NULL,
PkgExecStatus varchar(100) NOT NULL
)
SELECT * FROM SSIS_Logs
go
Ready
Made Logging: Failure
·
Goto
SSIS menu ->Logging->it will open configuration window
·
Select Provider type as -> SSIS log
provider for SQL server and add
·
Select connection in configuration.
·
In details tab select the event types like
on error, on task failure.
·
Sysssislog table will be created in system
tables to get the details of the package failure details.
Tardis_stage DB-> System Tables
-> sysssislog
File
Existence: (Whether File Exists or Not)
1. Check
whether source folder has files or not
2. If
file exists,
a. Load
the data
b. Move
the file to Archive folder by adding time stamp
3. If
file does not exists,
a. Log
the details in table
C#
code:
//Declare variable
string x;
//Assign the value to variable
x = Dts.Variables["User::FilePath"].Value.ToString();
//Check whether file exists or not
//If exists return TRUE or FALSE
Dts.Variables["User::FileFlag"].Value = File.Exists(x);