Generate a GUID/newid() in SSIS 2008

Inexplicably, SSIS doesn't natively include a method for generating new GUIDs in the Derived Column Data Flow Transformation.

The get-around is to create a custom Script Component. In SSIS 2008, we have a choice of using Visual Basic or C# to write the script code; I'm going to demonstrate a C# script.

Let's say we have a Data Flow Source and a Data Flow Destination.
(you can click on these pictures to enlarge):



For the sake of this demo, the only difference between the source and the destination is that the destination has a GUID column, with no default constraint which adds its own NEWID() on insert. So we need to generate the GUID in SSIS. The usualy was we'd add a new column worth of data is by using the Derived Column transformation, but as I've mentioned (and you've probably found if you're reading this) there isn't a new GUID option.

So we add a script component.



Choose the "Transformation" option, hit OK, and drag the Source's green arrow onto the Script Component.

Double click the script component, then "Inputs and Outputs", then expand Output 0 and Add Column. Call the column whatever you like, but I'm going to call it "SQLNinjaGUID" so you can see how it's referenced in the C# code. Change the data type of the column to unique identifier [DT_GUID].



Jump back to the script tab, make sure we're using Microsoft Visual C# 2008 as the ScriptLanguage, Edit Script, and replace ALL of the code with the following.

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

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.SQLNinjaGUID = System.Guid.NewGuid();
    }

}


Now when you drag the Script Component's green arrow onto your Data Flow Destination, you should see SQLNinjaGUID (or whatever you called your new column) pop up as an available Input Column in the destination's Mapping tab.

Warning: Don’t play with Reporting Services’ SQL Agent Jobs! Or How to Tell Which Job is Doing What

Having a look through SQL Server Agent’s list of jobs yesterday, I got a bit upset. About half the jobs in there are named things like “0343229B-0642-4E38-B7A5-C603C1F45976”. They’re Reporting Services Subscription jobs. Once again RS looks like a half-arsed product.

So I decide to go about renaming them, figuring that RS’ subscriptions will still be able to recognize the jobs as I’m only changing their names, not their IDs. Bad move. RS uses the names, can’t find the jobs when you restart the service, and recreates all those jobs with new GUIDs. Worse yet, I started getting “Only members of sysadmin role are allowed to update or delete jobs owned by a different login” errors whenever I tried to update subscriptions through the Report Manager, forcing me to have to play around with login permissions and job owners. A nightmare!

The script I wrote to help me recognize which job fires which subscription is below. It’s now more useful than ever:

NB: The CASE statement which transforms the “DaysOfWeek” int figure into actual days of the week doesn’t cover ever possible case, but it covered my needs. For a rundown of how this int works, see “Toolman’s” post at http://www.sqlservercentral.com/Forums/Topic501408-150-1.aspx

Also thanks to “stevefromOZ” from whose post at http://www.sqlservercentral.com/Forums/Topic254010-150-1.aspx I nabbed the email address part of the code below.

USE ReportServer
SELECT
         sysjobs.name,
   'RS - '
         + Catalog.Name
         + ' ['
         + CASE
            WHEN DaysOfMonth IS NOT NULL
            THEN CAST(DaysOfMonth AS VARCHAR(10)) + ' Day of Month'
            WHEN DaysOfWeek = 1 THEN 'Monday'
            WHEN DaysOfWeek = 2 THEN 'Tuesday'
            WHEN DaysOfWeek = 4 THEN 'Wednesday'
            WHEN DaysOfWeek = 8 THEN 'Thursday'
            WHEN DaysOfWeek = 16 THEN 'Friday'
            WHEN DaysOfWeek = 32 THEN 'Saturday'
            WHEN DaysOfWeek = 64 THEN 'Sunday'
            WHEN DaysOfWeek = 62 THEN 'Monday - Friday'
            WHEN DaysOfWeek = 120 THEN 'Wednesday - Saturday'
            WHEN DaysOfWeek = 126 THEN 'Monday - Saturday'
            WHEN DaysOfWeek = 127 THEN 'Daily'
   END
         + ' '
         + CAST(DATEPART(hh,Schedule.StartDate)AS VARCHAR(2))
         + CASE
            WHEN LEN(CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))) = 1
            THEN ':0' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   ELSE ':' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   END
         + ']' [NewName]
   FROM
         msdb.dbo.sysjobs
         JOIN
         dbo.ReportSchedule
         ON sysjobs.name = CAST(ReportSchedule.ScheduleID AS VARCHAR(255))
         JOIN
         dbo.Schedule
         ON ReportSchedule.ScheduleID = Schedule.ScheduleID
         JOIN
         dbo.Catalog
         ON ReportSchedule.ReportID = Catalog.ItemID
   ORDER BY
         Catalog.name