Load Integration Services Assembly File Into Visual Studio 2010 Project

SQL Server Integration Services 2012 comes with a new API for scripting packages which is called MOM – Managed Object Model. This API is accessed through the Microsoft.SqlServer.Management.IntegrationServices.dll assembly file. However, when you create an SSIS 2012 project/package using Visual Studio 2010, the assembly file is missing.
In this post, I will show you one of the ways of loading the assembly into project.
Requirements

• Microsoft Visual Studio 2010
• SQL Server 2012

We begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category.
After you have assigned a project name, proceed to click and drag the Script Task into Control Flow pane from toolbox.
I have called the package “sS_LoadAssembly.dtsx” and the Script Task “Scripting SSIS 2012″ as shown below.

In Solution Explorer, right click the package “sS_LoadAssembly.dtsx” as shown below.

Click “View Code”
An XML file called “sS_LoadAssembly.dtsx[XML]” is opened
Take note of the elements under node “DTS:ObjectData” – there is currently a single element called “ScriptProject”

Let’s go back to the file called “sS_LoadAssembly.dtsx[Design"
Right click the script task and click on “Edit”
Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2010".
Click Edit Script.
Close the script.
Save the changes.
Go back to the "sS_LoadAssembly.dtsx[XML]” XML file.
You will notice that additional elements have been addedd under node “DTS:ObjectData”

We are interested in the node called “ItemGroup”
Let’s add a reference to the Microsoft.SqlServer.Management.IntegrationServices.dll assembly file as follows:

Save all changes.
Go back to the file called “sS_LoadAssembly.dtsx[Design”
Right click the script task and click on “Edit”
Click Edit Script.
Collapse “Namespaces”

Insert the following:

• using Microsoft.SqlServer.Management.IntegrationServices;

Now you can go ahead and access the new API for scripting SSIS 2012.

SSIS Expression to get filename from FilePath

Source : Beyond Relational

How to extract the file name from a file path where the number of sub folders or name of folder is not fixed. In simple terms you do not know the position where the file name starts.
Typical example: You have a foreach loop set to traverse sub folders and you set the retrieve file name to “fully qualified” and in parameter mapping you map this to a string variable strFilePath. You use this variable to set up the connection string for may be a flat file source(will not be discussing that). Now lets say you need to retrieve the file path for logging purpose or sending a mail. How do we do this??
Solution: Have another variable and name it as strFileName. Goto the properties of this variable by selecting the variable and clicking F4. Set the property EvaluateAsExpression to true. The Expression property becomes enabled. Put the below expression there:
REVERSE(SUBSTRING(REVERSE( @[User::strFilePath] ), 1, FINDSTRING(REVERSE( @[User::strFilePath] ),”\\”, 1)-1))
Lets say our file path is: C\Folder1\folder2\file.txt
Lets break this expression into parts -

REVERSE
(
 SUBSTRING
 (
  REVERSE( @[User::strFilePath] ),
        1,
        FINDSTRING
        (
   REVERSE(@[User::strFilePath] ),
            "\\",
            1
        ) -1
    )
)

we use the expression REVERSE(@[User::strFilePath]) it will reverse the string within the parenthesis in our case the file path. So the output of this segment would be:

“ txt.elif\2redlof\1redloF\C” Let us assume the output of this expression REVERSE(@[User::strFilePath]) = revPath for simplicity sake.
Now our work becomes simple just find the first instance of back slash and read the string from the first position to the first instance of a back slash.
Now what would be the expression to do the above: SUBSTRING(revPath, 1, FINDSTRING(revpath, “\\”,1)-1 )
If you notice after the Findstring I do a minus one that is done to remove the back slash else even that would be there in the output which is not required.
The output we get from the above expression is: ” txt.elif“
I hope now things look simpler all we need to do now is do another REVERSE function call to reverse the above string to file.txt
UPDATE
Thanks to Mario Puskaric, a simpler expression for this is:

RIGHT(@[User::strFilePath],FINDSTRING(REVERSE(@[User::strFilePath]),"\\",1)-1)

Sequential Container Task in SSIS

In this article we are going to see on how to use a Sequential Task container. This container is used in areas where the process needs to follow certain tasks sequentially. Sequential Task groups the tasks into multiple control flows and executes the process sequentially. We can use this task widely based on our requirement like, disabling a sequence when it should not process, use it when managing multiple tasks at a same time in one location. We can easily disable a set of tasks under sequential task by disabling the sequential task alone which is straight forward. If there are many tasks in our package we can group them based on their sequence and used to collapse and expand them and also to enable and disable them easily.
Once we are into the BIDS, now we will start with drag and dropping a sequential container task on to the designer view as shown in the screen below

 
Now add a variable as shown in the below screen.


  

Now we need to create a scrip task by drag and dropping it and double click on it will open the below screen. Here we need to add the variable to the Readonlyvariable as shown below




Now click on the Design Script button, which will open the below window where we need to write script as shown below in the main method.



  
Now add a sequential container flow for each day of week as shown in the below screen and connect to the script task


  
Now click on the green arrow of each task and do the same as shown in the below screen shot for different days



 After you have given the expressions for each task your screen look like below
Now right click and execute the package. Or Press F5 directly.

Deploying ASP.NET Websites on IIS 7.0

Introduction to IIS 7.0

Microsoft Internet Information Services (IIS) 7.0 in Windows Server 2008 and Windows Vista provides a secure, manageable platform for developing and administrating and hosting Web applications and services. It has been completely redesigned and Restructured .IIS 7.0 provides features and functionality for administrators to effectively manage Web infrastructures; developers to rapidly build Web applications and services; and hosters to Web hosting.

Features of IIS 7.0

Following are some features of IIS 7.0:

• IIS 7.0 provides features and functionality that enable administrators to reliably and effectively manage Web infrastructures.
• IIS 7.0 has a distributed file-based configuration system that enables IIS settings to be stored in web.config files along with the ASP.NET settings.
• IIS 7.0 provides a cost-effective, more scalable Web server platform for delivering reliable Web hosting to a broad set of customers.

Major innovations in IIS 7.0:

• A modular, extensible core Web server
• A unified, distributed file-based configuration system
• Integrated health monitoring and diagnostics
• A set of new administration tools with delegation support

For more features and product understanding, check here.

Basic Architecture of IIS 7.0

The following diagram shows the Overall Architecture of IIS 7.0 which contains HTTP.Sys, SvcHost.exe, Application Pool and Worker Process(W3Wp).
The main components of IIS 7.0 are HTTP.Sys, Svchost.Exe, Application Pool , Worker Process (W3WP.exe) and Configuration Store.
HTTP.Sys: It the Kernel mode Protocol stack which listens to the HTTP and HTTPS Request. W3SVC and WAS are the parts of Svchost.exe. W3SVC is the Listener of Request from kernel mode that is passed by the HTTP.Sys. W3SVC also interacts with Windows Activation Process which is managed by the worker process by starting, stopping and recycling the application pool. It was also responsible for Health Monitor of Application Pool during runtime. Configuration stores all web.config and ASP.NET Settings and other configuration in XML Hierarchy form. W3wp.exe is a long-running process that processes requests and generates responses.
The following diagram shows you the process flow of IIS 7.0. This is the flow of User Request to IIS and gets the Response from IIS.
User Request Pass from Kernel Level to User Level Via Http.Sys and then passes to svchost, and then goes to Application Pool.
For more details, please click here.

How to Deploy ASP. Net Websites on IIS 7.0

From now onwards, I will describe one example to deploy your ASP.NET websites on IIS 7.0.
Step 1: From Visual Studio, publish your Web application.
Step 2: Copy the published application folder to "C:\intepub\wwwroot" [default] folder.
Step 3: From RUN - > inetmgr -> OK
The following screen will come. This is the main page for any application. There are three panels.
"TestWeb" is a recently pasted webapplication on your wwwroot folder.
Step 4: We need to convert it to an application, just right click and then Click on "ConvertToApplication" as shown in the following picture:

After converting it to application, its icon will be changed and then you can set the property for your web application from the middle pane. You can set IIS Authentication Mode, Default Page Just like IIS 6.0:

You can change Security Settings on Authentication Section. By default Setting will be set from your web.config itself. As in my web.config Form Authentication was set that's why, Forms Authentication has been Enabled. If we want to change the status, just double click and update status to Enabled to Disabled or Vice Versa.
Now one most important part is to set the Application Pool for your application.

How to Create Application Pool

A site can contain many applications including that site’s default application, which is called the root application. In addition to belonging to a site, an application belongs to an application pool, which isolates the application from applications in other application pools on the server .
Step 1: Right Click on Application Pool and give the name of your pool. Here, I have given "pool" and select Framework and click on OK:
Step 2: If you are writing some thing on the server [may be writes Error Events Logs], you need to change the Pool Identity to Local System. Right click on Pool Identity setting as shown in the following image:
There are three identities of Application Pool:

• Network Service
• Local Service
• Local System

Assign Application Pool To Your Application

Now, we need to assign the Application Pool to our web application.
Step 1: Right Click on "TestWeb" Application and Select "Advanced Settings":
Step 2: Finally assign your created Application Pool "pool" to "TestWeb" Application.
Now you can run your application by just typing http:\\localhost\testweb and it can be accessible on network by IP also.

Configure Web Gardens in IIS 7.0

I have split this section into some parts. These are:

• What is Application Pool?
• How To Create An Application Pool and Assign To a Web Application
• What is Web Garden?
• How To Create Web Garden?
• Is it Recommended to use Web Garden ?

What is Application Pool ?

Application pools are used to separate set of IIS worker processes that share the same configuration. Application pools enable us to isolate our web application for better security, reliability, and availability. The worker process serves as the process boundary that separates each application pool so that when one worker process or application is having an issue, other applications or worker processes are not affected.

Figure: Application Pool With Worker Process On IIS Server

In your IIS, there may be more on Web sites hosted and by creating Application Pool, you can just assign a Separate Worker Process to your application. I have already discussed how you can assign Application Pool to your web application.

Types of Application Pools

There are two predefined Application Pools is IIS 7.0:

1. DefaultAppPool (Integrated)
2. ClassicAppPool

None of the Application Pools are Installed by default when IIS 7.0 is installed. Classic Application Pool is installed when ASP.NET 2.0 is installed in the System.

Identity Of Application Pool

Application pool identity configuration is an important aspect of security in IIS 7.0, because it determines the identity of the worker process when the process is accessing resource. This Settings comes form IIS 6.0. in IIS 5.0. There the Application pool runs under the local system account. In IIS 7.0, there are three predefined Identities that are the same as IIS 6.0

Applicationpool Identity	Description
LocalSystem	LocalSystem is a built-in account that has administrative privileges on the server. It can access both local and remote resources
LocalServices	LocalServices built-in account has privileges of an authenticated local user account. It does not have any network access permission.
NetworkServices	This is the default Identity of Application Pool. NetworkServices has privileges of authenticated local user account and it can have access remote resource as machine account.

How to Create An Application Pool and Assign It to a Web Application?

This has already been discussed.
Creating Application Pool and Assigning it to a Web Application

What is Web Garden?

By default each Application Pool runs with a Single Worker Process (W3Wp.exe). We can assign multiple Worker Processes With a Single Application Pool. An Application Poll with multiple Worker process is called "Web Gardens". Many worker processes with the same Application Pool can sometimes provide better throughput performance and application response time. And each worker process should have their own Thread and Own Memory space.

Fig: General Block Diagram of Web Garden.

As given in the picture, in IIS Server there may be multiple Applicationpool and each application pool has at least a single Worker Process. Web Garden should contain multiple Worker processes.
There are certain restrictions to use Web Garden with your web application. If we use Session Mode to "in proc", our application will not work correctly because session will be handled by different Worker Process. To avoid this type of problem, we should have to use Session Mode "out proc" and we can use "Session State Server" or "SQL-Server Session State".
Main Advantage: The worker processes in a Web garden shares the requests that arrive for that particular application pool. If a worker process fails, another worker process can continue to process requests.

How to Create Web Garden?

Create a New Application Pool. Right Click -> Advance Setting -> Go To Process Model Section, set the Value for Maximum Worker Process.

Is It Recommended to use Web Garden?

It not always recommended to use Web Garden to your Application. It effects the performance of your sites except in very specific cases like some long running synchronous requests or application is very unstable.

IIS 6.0 Vs IIS 7.0

The main advantages of IIS 7.0 is Modular Design which gives some benefits to IIS 7.0 over IIS 6.0. The following table shows you some differences of IIS 6.0 and IIS 7.0.

Features	IIS 6.0	IIS 7.0
Architecture	Monolithic	Modular
Setup	Most Features Installed	Minimum installation based on role
Extended Features	ISAPI Filter and ISAPI Extension	Added Module and handler in managed or native code
Customize UI	Normal	Customize for .NET

There are many more differences.

Where Do I Get IIS 7.0

IIS 7.0 will not be available with all Operating Systems. There are some specific OS and specific Versions for IIS 7.0.

Operating System With Edition	Available
Windows Server 2008	Yes
Windows Vista (Home Basic)	No (Default) need to install
Windows Vista (Home Premium)	No (Default) need to install
Windows Vista (Business)	Yes
Windows Vista (Ultimate)	Yes

Avatar