It contains container and control flow tasks in ssis
Container
It contains other tasks
Eg: for each loop container
For loop container
Sequence container.
There are normal (and) as well as maintenance tasks.
It executes the underlying task to the specified no.of times. [iterative count we know here]
It has 3 sections.
1. Initial section: The variables are initialized in the container.
2. Assignment section: The variable incremented or decremented.
3. Evaluating section: Here the condition verified.
→ For loop requires a “variable” to do the operations.
Create variable:
SSIS menu → variable → add variable →
Name: counter
Data type: integer
Take for loop container on control flow → Rc → Edit
INITIAL Expression:@ counter=0
Eval.Expression:@ counter<5
Assign Expression:@ counter=@counter+1
Take data flow
Take flat file source (to be copied) and OLEDB destination connect it.
Note: the above eg in for loading a file data 3 times to table
Navigation:
Take for each loop container → rt click → edit
Enhance your skillset and give a boost to your career with the SSIS Certification Training Course.
Enumerator: For each file enumerator.
Folder: c:outputGroup
File: “.TXT”
Retrieve file name: select fully qualified select transverse folders.
Variable –Mappings:
Variable drop down → select new variable →
Name: group var → ok.
Take data flow task in the for each loop container.
Take flat file source → specify one file in the group
Go to source connection manager → rt click → properties → expression → click ellipse
Property drop down list: select connection string.
Expression: click ellipse
Variable: drag and drop group var to the expression section.
Okàok.
Take OLEDB destination and connect.
It performs file and folder options such as coping, moving, deleting, creating…etc.
Eg: 1. moving the files from folder ‘x’ to folder ‘y’
2. in above for each loop container example load successive files into another folder [i.e., which are not loaded that can be easily tracked]
Navigation:
File system task → rt click → edit →
Destination:
Is destination path variable: false
Destination connection: Browse to success folder
Over write destination: false.
Operation: move file
Source:
Is source path variable: false
Source connection: browse to source connection manager (which was taken at data flow task source level)
It executes the packages which are available in “file system” and “SQL server database”.
This is designed to execute another package with in main package. We can control the flow between these packages.
1. Take execute package task on control flow
2. Rt click → edit →
Location: file system
Connection: specify desktop any folder package
Password: if password is there, specify
Execute out of process: false
Note: execute out ok of process ‘true’ means, each package runs with a separate process.
It execute SQL of any database (oracle, teradata, excel, SQL server….etc).
To connect to the corresponding database we must specify the corresponding type.
It executes queries, commands in the corresponding database.
Note: the execute out of process option
v If we turn into true the sub package runs separately from the main package process.
Navigation:
Take execute SQL task on control flow.
Rt click → edit
Connection type: OLEDB
Connection: local host: DB-MSBI
SQL statement: delete from party;
By pass prepare: true
If select “false” instead of “true”, in this situation SQL converts into the another query and it run server time on the target data base.
Create procedure [dbo].[samp] @ PID integer, @PName VARCHAR (30) AS BEGIN Insert into for each-tab (partyID, PartyName)
Values (@PID,@PName);
END
EXEC SAMP 10, KKK
Select *from for each-tab;
Drop procedure samp.
→ SQL statement: EXEC SAMP 20, triven
Script task:
It supports the scripting of VB.Net or C#.Net
⇓
Execute SQL task
Rt click → edit
Script language: Microsoft visual basic 2008.
Click edit script → add the below statement in the main ()
MsgBox (“MSBI class”)
Save → ok.
Real time:
Eg: loading multiple work sheet data in a single excel sheet to a table.
Bulk inserts task navigation:
Rt click → edit →
Destination connection:
Connection: local host: DB-MSBI
Table: party
Format:
Row delimiter: {CR}-{LF}
Column delimiter: ,(comma)
Source connection:
File: browse the file
It takes backup of SQL server databases
Backup → rt click → edit → back up data base task →
Name: SRC
Backup: full
Data bases: DB-MSBI
Backup to *Disk: tape
Create a back file for every database → click ok.
This is designed to send e-mails to corresponding recipients.
It requires an SMTP server. (Simple mail transfer protocol)
send mail task → Rc → edit
Navigation:
send mail task → Rc → edit → Mail → SMTP connection → New → specify name → SMTP server (IP address) → click ok.
From (which user) → to (which user) → subject (job successfully finished)
Message source type: direct input → priority: high → click ok.
Active script task: It parses and executes active scripts.
Analysis services execute DDL Task: It executes DDL operation of analysis services.
Analysis service processing task: This is used to process the data of facts cube, dimensions in analysis services we use this task.
Execute DTD 2000 package task: It executes win 32 executable tasks.
It perform file operation such as sending, receiving files.
Transfer databases/error messages/jobs/login/master stored procedure SQL server object task.
→ The above tasks transfers the specified objects from “one SQL server instance” to “other SQL server instance”
SSRS | Power BI |
SSAS | SQL Server |
SCCM | SQL Server DBA |
SharePoint | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |
Name | Dates | |
---|---|---|
MSBI Training | Oct 12 to Oct 27 | View Details |
MSBI Training | Oct 15 to Oct 30 | View Details |
MSBI Training | Oct 19 to Nov 03 | View Details |
MSBI Training | Oct 22 to Nov 06 | View Details |
As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.