SSIS in Yukon - Part II - Creating a Simple Package in SSIS

In the previous article we had discussed about the new IDE for SSIS.
Now let us see how to create a simple SSIS package. The following are the steps:

1. From the File menu, click New-> Project.
2. Under Projects, choose Business Intelligence Projects and under Templates, choose Integration Service Project, and then click OK.
3. By default a package named Package.dtsx is created under the folder SSIS Packages. Click View-> Solution Explorer to see this.
4. Right click on the Connections pane, and then click New OLE-DB Connection.
5. Create two new connections - one to your Yukon source server and another to your Yukon destination server. Name these connections as MySourceConnection and MyDestinationConnection.
6. From the View menu, click Toolbox.
7. Now drag and drop a Data Flow Task component on the Control Flow pane.
8. Double-click the Data Flow Task component to take you to the Data Flow pane.
9. From the toolbox, drag and drop a OLE DB Source component and OLE DB Destination component. Name them as MySource and MyDestination respectively.
10. Double-click MySource.
11. Set the connection of MySource to MySourceConnection.
12. Set the Data-access mode to Table or View.
13. Set the name of the Source table from the listed tables of the database.
14. By clicking on the Columns tab on the left, you will be able to see the list of columns for the table. You can uncheck the columns that are not needed and then click OK.
15. Set the properties of MyDestination OLEDB component in the same way as in 14.
16. Click on MySource component. Drag the Green arrow you notice and make it point to MyDestination component. You have now connected the Source and the Destination components.
17. From the Debug menu click Start to run your first SSIS project.

Once your package has executed successfully, you will see components (Dataflow task, MySource and MyDestination) coloured in Green. And if there is any failure during execution, the components are coloured in Red. The success or failure of the package can be monitored by clicking on the Progress tab in the project during execution.

Note: The Source and Destination components must contain the same number of columns and same column names (case sensitive) to avoid Validation errors.

You can also have Event Handlers at the Package level or for each component in the Event Handler tab.

So this is how we go about creating a simple package. My next article will focus on how you can programmatically create these SSIS packages using Whidbey.

SQL Server Integration Services (SSIS) in Yukon

Many of us are familiar with Data Transformation Services (DTS) in SQL Server 2000.

SQL Server 2005 (code named Yukon) provides a completely new platform called SQL Server Integration Services (SSIS) that serves as the primary enterprise platform Extract, Transform and Load (ETL).

There are a looot of enhancements that has happened to DTS in Sql 2000. Creating packages is now separated to a new Development environment called Business Intelligence Development Studio (bienv.exe). This gives the same look n feel as our Visual Studio Dev Environment.
We have everything similar to VS - the Solution, Project, toolbox, setting configurations, etc.

The toolbox contains various tasks and componets that can be used in the editor.

There are 4 tabs provided in the editor:

1. Control flow - For controlling the task flow
2. Data Flow - For Data pumping tasks
3. Event handlers - For handling events and errors
4. Package Explorer - Gives a treeview view of the various components in the package
5. Execution results - For viewing the status during execution

The connections form a separate section that is common to the above four tabs. Connection manages for File, SQL Server, OLE DB can be added here.


Another cool feature is the Logging feature. All we have to do is just Right click the editor pane and enable Logging with a specific connection. All events now get logged in the specified server.

Every package that is a part of a SSIS project can be executed from BI. The deployment of these packages can be done either to the file system or to SQL Server. The packages deployed in SQL Server will be saved in the DTS Server.

My next article will throw more light on working with SSIS and programmability in Whidbey for SSIS.

HOW TO : Call a C# function inside Yukon Stored Procedure

Recently I had an interesting requirement in my project to use a C# procedure inside SQL.
Prior to SQL Server 2005, we had to use only Extended store procedures..
But as Yukon is integrated with CLR, you can now call a C# Whidbey function inside SQL Server procedure.

The following is the way you can do it:

1. Start Visual Studio 2005.
2. On the File menu, click New Project.
3. Under Projects, choose Visual C# and under Templates choose SQL Server Project template. Click OK. By default a project named SqlServerProject1 is created.
4. You will now be prompted to specify a connection. Provide the connection of the Yukon server for which you need the C# function.

5. Right click SqlServerProject1 on Solution Explorer and click Add New Item. Choose any template that is provided.
6. Now replace the following code with the existing code provided with the template:

#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
#endregion
public class MyClass
{
public MyClass()
{
}
public static void GetDepartmentDetailsByNum(int DeptNo)
{
SqlPipe sp = SqlContext.GetPipe();
sp.Send("The Department number is " + DeptNo.ToString() + "\n");
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "select * from dept where deptnum=" + DeptNo.ToString();
SqlDataReader rdr = cmd.ExecuteReader();
sp.Send(rdr);
}
public static void GetSum(int x, int y)
{
SqlPipe sp = SqlContext.GetPipe();
int z=x+y;
sp.Send(z.ToString());
}
}
7. Now save the project and Build the project.
8. Open SQL Server Management Studio and connect to the Yukon server.
9. Right click the Database where the C# function is needed and then click New Query.
10. In the query editor, paste the following code:

CREATE ASSEMBLY ManagedCsharpProcedure
FROM 'C:\Laksk\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

CREATE PROCEDURE [dbo].[GetSum]
@x int ,@y int
AS
EXTERNAL NAME
SqlManaged.DeptDetails.GetSum
GO

11. Now test the GetSum procedure using the following statement :
exec GetSum 5,4

Thats it !!! You now have a C# method being called in SQL Server. The same method can also be used with recordsets as in the C# function GetDepartmentDetailsByNum that is included in the same class. So on passing the Dept Number, we get the Department details through the C# function.