Pages

SSIS - For Loop Container

This works on the tradition counter logic, where it has 3 steps.
1. Intialization,
2. Incrementation,
3. Condition.
Let us see in detail, Stepwise...
. Drag and Drop a For Loop Container from the toolbox to the Control Flow Tab
. Select the Container, add a variable from the Variable box. Variable box can be obtained from the Menu SSIS->Variables. say variable name is "count" of data type int.
(When we select the container and create a variable the scope of the variable is set only for Container level, means the variable will not be available for other components outside the container.)
. Right Click on the container and select Edit from the Context Menu displayed.
. In the InitExpression, Enter @[User::count]=0. this assigns the value 0 to the user created variable count.
. In the Eval Expression, Enter @[User::Variable] <10. this sets the loop condition.
. In the Assign Expression, Enter @[User::Variable] = @[User::Variable]+1. this increments the value of the variable by 1 for every loop.
. Select Ok and close the Dialog.
. From the tool box, place a script task in the container.
. Right Click the script task and select edit from the context menu.
. Goto script tab in the left panel.
. Click Design Script button located in the right bottom of the dialog. this opens a vb code page with new studio environment with some code writen already.
. Place the code MsgBox("hai"), in the main subroutine, before the assignment of task result.
. save a close the environment.
. Click ok and close the script edit dialog.
. Right Click the container, select execute container from the context menu.
. this runs the For Loop Container, and the tasks inside it. it displays the Hai message box 10 times and stops the execution.

This example explains the feature of the For Loop Container in a simple, how ever we can use this as per our need in the package.

SSIS - Expession Builder

It is an Editor, which creates expression using the variables and In-Built functions. Normally this editor can be reached for all elements in the package. the button with "..." [3 dots] is clicked to open this editor.
The property of the elements are set directly by assiging values to them, or by setting a expression and the expression is evaluvated in the runtime to set the values.
Almost every control's editor will have a Expressions tab, which can be selected, the tab contains a Field group named Expressions, by selecting the triple dot button again, will opens a Property Expression Editor. this contains two columns. first column contains drop down list, from which a field name can be selected and the second column contains a triple dot button, which actually opens a Expression Builder.
Expression Builder Editor contains 5 blocks.
1. Variable - list of variables available within the scope is listed.
2. Built in functions - list of functions are organized in groups and listed.
3. Description - displays a description of built in function, which is currently selected[Read only]
4. Expression - variables and functions can be dragged and droped here or expression can be manually keyed in.
5. Evaluvated Value - the value of the expression will be displayed, when the evaluvate expression is clicked, which throws exception if the expression is not set correctly.

The Built In Functions block will have type casting symbols and also the supported operators for evaluvating the expression.
once the expression is created succesfully the dialog can be closed, and the expression is assigned to the field. we can assign expressions to any no of fields.

SSIS - Precedence Contraints

. It is link created between the Containers, executables, tasks or any control flow elements.
. For example, to view a Precedence Constarint Editor, create two script tasks.
. select a task, it displays a arrows pointing downwards, drag and drop the arrow to the second script task.
. a conector is created in between the tasks. it is called Precedence constaint.
. the default value set to the constraint is success.
. to view the editor, select the connector, right click and select edit from the context menu.
. a dialog box opens with fields, evaluvation operation, constaint and expression.
. the flow can set as per the condition defined here.
. the condition can be set to Constraint, with following values,
1. Success - allows to flow only if the previous task executed succesfully.
2. Failure - allows to flow only if the previous task fail to execute.
3. Completion - allows to flow, without considering the execute status of previous task.
. the condition can be set to Expression. the flow depends on the result of the expression.
. the condition can be set to Constaint or Expression, where both will be defined and flow will be decided by any one.
. the condition can be set to Constraint and Expression, where both will be defined and flow will proceed only if both are passed.
. Multiple Precedence Constraints may contain for a task, in that case the logical Or and And between the Precedence Constraints can also be set using the editor.

SSIS - Control Flow Items

Control Flow Items contains Containers and Tasks.
. Containers are Components which holds the tasks and the task will be executed as per the containers nature.
. Tasks are activity which performs a action.
. SSIS supports various type of tasks related to database, file system, process, web service, FTP, Xml, Sql jobs, message queues.
. Each task can be ordered, or aranged in sequence like one after the other or in parellel using Precedence Constraints.
. Container helps to execute repeative tasks, and also to make a group of task over package.

Debug Visual C++ ActiveX DLL [Web component]

The ActiveX dll can be debugged using test container. It is a in-built tool with VC++, Visual Studio IDE. we can set the debug environment in order to use this. In the project properties, go to Debug section, then select the "command" property and set the value as test container from the drop down list. so, when the application is build and run, Test Container application is invoked and we can add the dll component from a avalable list of components. once selected and added in the container, the GUI part is it will be like a box, but the methods can be invoked and tested for functionality.
This is one way for testing using Test Container tool.
If the component is web based, then In project properties->Debug Section set the value to Internet Explorer for "command" property. once it is set and run, the build will be made and the Internet explorer will be opened with blank url. enter the url of the page where ActiveX component is used. this will opens the component in debug Mode. Before starting this, let us see the environment. the ActiveX component wil be installed for the first time in client system, or else when the page is opened the object will be downloaded and installed in the location as mentioned by the devoloper. once it is installed, it will used for succesive calls of the same page, untill client uninstalls the control. Before stating the debug process, the newly build dll and the corresponding pdb file has to be placed in the location where the application installed the component while it is rendered. Now the enviro is set and redy to work, Run the project, it opens the url. place the debug point wherever needed, enter the required page url in th eopened browser and it stops when the debug point reaches.
1. Set the Project property for Debug section->Command to Iexplore.exe
2. Build the component.
3. After successful build copy the dll and correspoding pdb file in the Component installed location.
4. Place the debug points as required.
5. Run the application, it stops at the Break point.

Access Clibboard using Javascript

In Internet Explorer we can access clipboard programatically, the same is not possible in Firefox browser. the security permission are more in firefox, so it will not allow a programatical access to the clipboard. but we can copy and paste contents in the form.
if we use code to do the pasting of data from external contents, we can get data using this way.
window.clipboardData.getData("Text")

By doing this way, we lose the format of the content we copied, to avoid this we have another way to do the same.
var range = document.selection.createRange();
range.execCommand("paste");

if we write code for paste this way, the format of the content we copied will be preserved while pasting. it also accepts the table format data.

What is SSIS package?

1. SSIS package is a collection of connections, control flow elements, data flow elements, event handlers and variables.
2. SSIS package can be built using Visual Studio 2005 by design mode by dragging and droping controls or programatically by adding the referece of SSIS components.
3. SSIS package can be stored in File System, SQL Server, SSIS package store.
4. Package is a unit of work that is retrived, executed and saved, either manually or on scheduled intervals of time or on specific day and time.
5. It is vast and lots of features involved, reliable and configurable.
6. Other major objects used with package to support on runtime are configuration, Logging and Event Handlers.

Event Handlers : It is an work flow that runs in response to the events raised in package, task or container.
Configuration : It is a set of property values used by the package, it enables the package to be customized.
Logging : It is a collection if information about the package, that is collected when it runs.
Variables : Supports system variables & user defined variables. it can be used in expression, script and configuration. each variable have scope depends its creation.