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.