SSIS IMPORTANT SCENARIOS

====================================================================
1ST SCENARIO:
ENSURE THE FOLDER AVAILABILITY ,IF IT IS NOT AVAILABLE CREATE IT


2ND SCENARIO: ENSURE FILE AVAILABILITY,IF AVAILABLE LOAD THE DATA OTHERWISE SEND AN EMAIL TO THE   MANAGER


3RD SCENARIO:
BASED ON EMP_DAILY DATA INCREMENT SAL WITH THE INPUT INCREMENT IN THE HISTORY TABLE.

4TH SCENARIO: SET OF FILES FROM A FOLDER LOAD TO A TABLE,MAINTAN AUDIT INFO  SUCH AS FILEPATH,TIME,USERNAME,FILESIZE AND ARCHIEVE OF FILE

5TH SCENARIO: CAPTURING OUTPUT VALUE FROM A PROCEDURE


6TH SCENARIO: CAPTURING OUTPUT AND RETURN VALUE FROM A PROCEDURE


7TH SCENARIO: MOVING DATA FROM FIXED LENGTH TO VARIABLE TEXT


8TH SCENARIO: INPUT DATA WITH SINGLE QUOTES AND OUTPUT NEEDED IN DOUBLE QUOTES.
=====================================================================

SCENARIO 1:

HOW TO LOAD MULTIPLE TABLES WITH THE SAME STRUCTURE TO A DESTINATION

1ST STEP:(CREATE THREE TABLES LIKE BELOW)

create TABLE S1(TID INTEGER,TNM VARCHAR(30))

INSERT INTO S1 VALUES(1,'XXX')

INSERT INTO S1 VALUES(2,'YYY')

--

create TABLE S2(TID INTEGER,TNM VARCHAR(30))

INSERT INTO S2 VALUES(3,'ZZZ')

INSERT INTO S2 VALUES(4,'PPP')

--

create TABLE S3(TID INTEGER,TNM VARCHAR(30))

INSERT INTO S3 VALUES(5,'GGG')

INSERT INTO S3 VALUES(6,'KKK')

 

SELECT * FROM S1;

SELECT * FROM S2;

SELECT * FROM S3;

---

2ND STEP:(CREATE AN INTERMEDIATE TABLE LIKE BELOW WITH THE LIST OF TABLE NAMES)

 

SELECT t.NAME,RANK() OVER(ORDER BY T.NAME DESC) AS TCNT INTO TEMP

  FROM sys.tables AS t

  INNER JOIN sys.schemas AS s

  ON t.[schema_id] = s.[schema_id]

  WHERE s.name = N'dbo' AND T.NAME <>'TEMP'

  ---

  SELECT * FROM TEE

  --DROP TABLE TEE

 

 

3^RD STEP:

CREATE PACKAGE WITH TWO VARIABLES AND A TASK FLOW LIKE BELOW

 

VARIABLE : I à NUMERICà1

          TNAME àSTRING àS1

 

 

SCENARIO 1:

HOW TO CREATE A DIRECTORY IF IT DOES NOT EXIST

 

Two ways to implement

1. Script task
2. Filesystem Task

 

2ND SCENARIO: ENSURE FILE AVAILABILITY,IF AVAILABLE LOAD THE DATA OTHERWISE SEND AN EMAIL TO THE   MANAGER;

Script task: 

    Language: C#

    Read Only Variable: User::File

    Read Write Variable  : User:: FileExists

    Write the below code

 

 

 

Script-> precedence constraint ((DT_STR, 10, 1252) @[User::FileExists]=="True")àDataflow

ScriptàPrecedenceconstraint ((DT_STR, 10, 1252) @[User::FileExists]=="Flase")—Send Mail

 

3RD SCENARIO:
BASED ON EMP_DAILY DATA INCREMENT SAL WITH THE INPUT INCREMENT IN THE HISTORY TABLE.

CREATE TABLE EMP_DAILY(EID INTEGER,ENM VARCHAR(30),INC INTEGER);

INSERT INTO EMP_DAILY VALUES(1,'VINAY',2000);

INSERT INTO EMP_DAILY VALUES(2,'MADHU',1000);

INSERT INTO EMP_DAILY VALUES(3,'RAJESH',2000);

 

CREATE TABLE EMP_HIST(EID INTEGER,ENM VARCHAR(30),ELOC VARCHAR(30),SAL INTEGER);

INSERT INTO EMP_HIST VALUES(1,'VINAY','HYD',10000);

INSERT INTO EMP_HIST VALUES(2,'MADHU','HYD',20000);

INSERT INTO EMP_HIST VALUES(3,'RAJESH','USA',30000);

5TH SCENARIO: CAPTURING OUTPUT VALUE FROM A PROCEDURE

 

create procedure totalincome (@loc varchar(20),@total integer output)

as

begin

select  @total=sum(sal) from emp_hist where eloc=@loc;

end;