Enumerators

Enumerators in MSBI

For each file enumerator: For each file enumerates is used MISSING LINE PG:53 to loop through a collection of files in the specified location.

For each item enumerator: For each item enumerator is used to enumerates or loops through items in a collection.

For each ADO. Net schema Row set enumerator: It is used to enumerate through schema inform nation about a data source.

Example: To loop through a list of tables from the specified data base

For each Node list enumerator: It is used to enumerates a collection of XML files

File system task: It can be used to perform certain operations on files such as copy file, more file and rename file and also perform operations on directories as well such as copy, create, delete and more directories.

Scenario : Execute multiple files and more (or) copy executed files to new directory.

Open business intelligence development studio     ---------->

Create a new package and rename it as for each loop .dtsx     ---------->

In control flow, select variable from rig click context menu and define the following variables

UV source path                 String                           D:/SSIS packages/packages

UV Packages TO Run        String

UV Full Path                      String    ---------->

In variables window, select UV full path    ---------->

Press F4 for properties, set the following settings evaluate as expression – True

Expression                         -           Provide the following expression

Inclined to build a profession as MSBI Developer? Then here is the blog post on
 MSBI Training Program.

1. [USER:: UV Source Path] + “11” +” executed on –“ +

(DT_WSIR,20)(DT_DB DATE)@ [SYSTEM:: Start Time]    ---------->

Close properties window, copy any .dtsx files at the specified locations

D:/SSIS packages/packages    ---------->

In control flow, drag and drop file system task and rename it as FST    ---------->

Create a new directory with today’s date    ---------->

Double click on file system task and provide the following settings

Operation                                      -                                   create dictionary

IS Source path variable                 -                       True

Source variable                             -           USER:: UV Full path

Use directory if exists                   -           True    ---------->

Drag and drop for each loop container and make a connection between file system task and for each loop container.    ---------->

Double click on for each loop container to edit it    ---------->

In collection page,

Enumerator – select for each file enumerator

Folder – specify path where, the files are located,

D:/SSIS packages/packages

Files - * dtsx

In variable mapping page.,

Select USER :: UV package to run variable from ddl.    ---------->

Click ok    ---------->

Drag and drop execute package task on to for each loop  container    ---------->

Double click on execute package task to edit it    ---------->

Location – file system    ---------->

Connection – new connection    ---------->

Click Browse    ---------->

Select any package (Test condition .dtsx) and click open and click ok.    ---------->

In connection manager section, select test condition .dtsx.    ---------->

Press F4 for properties    ---------->

Expression – click browse    ---------->

Select connection string property name and click browse    ---------->

Expand variable in edit expression editor    ---------->

Learn more about MSBI Interview Questions in this blog post.

Drag and drop USER:: UV package to Run variables on to expression section    ---------->

Click evaluate expression to check the syntax of  expression    ---------->

Click ok twice    ---------->

Drag and drop file system task on to for each loop container    ---------->

Make a connection between execute package task and file system task.    ---------->

Double click file system task, produce the following properties.

Operation              -           copy file

Is source path variable – False

Source connection   - select test condition .dtsx

Is Destination path            -           True

Variable

Destination variable – USER :: UV Full path

Over write destination – True    ---------->

Click ok    ---------->

Execute package

For an Indepth knowledge on MSBI click on:

• SQL Server Analysis Services
• Report Server
• Transfer SQL server Objects Task
• Flat file source
• Slowly Changing Dimension