SSIS and LINQ, continued.

A solution that seems to work.

I've been dorking with SSIS and Linq the last couple of days, and it's been enjoyable.  I tried an experiment with the "ORM" tool for Linq in a blog but that didn't work as I initially expected, and certainly needs some more work.  I probably should have posted this particular blog first, because this seems to be a viable solution for using Linq inside of SSIS; whether or not the performance is good or if it makes sense is yet to be determined.  TBD!  At any rate,  I'll combine what we did in the demystify script component blog, part 1 and create a data source as a linq query to a SQL Server database.  Sweet!

In order to run this, you'll need SQL Server 2008 Integration Services and the Adventure Works OLTP database. I use c# in all of my stuff, but this can be rewritten in vb.net for sure. First, create a database called Test if you don't have one already, and then run this script. We'll land the data here from the AdventureWorks HumanResources.Employee table.  Note: because I'm experimenting to a degree here, I'll leave off the PK which allows me to run this over and over, so thanks in advance for no nasty Joe Celko-esque emails that my DDL stinks.

USE [Test]
GO

/****** Object:  Table [dbo].[Linq_Employee]    Script Date: 03/07/2010 09:32:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Linq_Employee]') 

AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Linq_Employee](
    [EmployeeID] [int] NULL,
    [Title] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO

Now go open up BIDS, get to an SSIS project, add a package, add a Data Flow task to the Control Flow tab, and drag a Script Component over to the Data Flow tab. Remember that you first need to add that output buffer to the "Inputs and Outputs" of the Script Transformation Editor as well.  I called mine OUTPUT.  Go back to "Script" crack open the Edit Script, and paste the following code.  It goes without saying - but I'll say it anyway - that you need to change your server name, unless you also have a (Hyper-V) SQL Server named SQLMV1:

/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;
using ns_Employee;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();        
    }
    public override void PostExecute()
    {
        base.PostExecute();      
    }

    public override void CreateNewOutputRows()
    {
        DataContext db = new DataContext(@"Data Source =SQLVM1; 

                                 Initial Catalog=AdventureWorks; 
                                 Integrated Security = SSPI");
        Table<Employee> Employees = db.GetTable<Employee>();
        var EmployeeResults = from e in Employees
                    select e;

        foreach (var emp in EmployeeResults)
        {
            OUTPUTBuffer.AddRow();
            OUTPUTBuffer.EmployeeID = emp.EmployeeID;
            OUTPUTBuffer.Title = emp.Title;
        }
    }
}

Notice that I have a line using ns_Employee. This is because I've been dorking with this thing today and decided to create a separate entity class file for my object. So, either right click on the project in the script component, do an Add Class to add a separate file, name it Employee.cs, and add the following, OR optionally just add the class above or below the class in your main.cs file:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;

namespace ns_Employee
{
    [Table(Name = "HumanResources.Employee")]
    public class Employee
    {
        [Column(IsPrimaryKey = true)]
        public int EmployeeID;

        private string _Title;

        [Column(Storage = "_Title")]
        public string Title
        {
            get { return this._Title; }
            set { this._Title = value; }
        }
    }
}

Ok, we're almost there...now simply create a connection to a source destination (I used OLE), map the script output, and fire it up!  Booyah!!!

Lee Everest, M.S.

------------------------------

SSIS and Linq - Who'd a thought?

Join my facebook group and let's chat: