But what if you want to just call an existing SQL Agent job and actually wait for the job to finish (success or failure)? There isn’t anything that I could see built in to SSIS to do this, sp_start_job is asynchronous, so you are out of luck there. I figured I could call sp_start_job, then create a for loop in SSIS and just check the status every X seconds/minutes, but I would have to either make this a package I could use everywhere or reproduce the same logic in multiple solutions, so I shied away from that solution.

What I decided to do was build a custom SSIS control flow task in .NET that will execute a SQL agent job and check the status and wait until it finishes. A disclaimer: This is going to be a lot of code :) also, it could be improved (but what couldn’t?) – this was a 1.5-2 hour experiment.

First, I created a VS2008 C# class library. I tried adding a UI to my task, but I couldn’t get it working so there is some code there for that but it’s commented out.

here is what my solution looks like:

import the correct namespaces:

 
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Net;
using System.Net.NetworkInformation;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime.Design;
using System.Data.SqlClient;

Next, you need to create the actual skeleton/wrapper for your component. You can see I have two properties, job name, server name. It could be expanded to have the connection string or use an existing connection in SSIS, I wasn’t that ambitious. The “Execute” method basically just calls some functions and waits for result.

 
namespace ExecuteSQLJobAndWaitControlTask
{
 
    [DtsTask(
        Description = "Execute SQL Job And Wait", 
        DisplayName = "Execute SQL Job And Wait", 
        TaskContact = "Steve Novoselac",
        TaskType = "SSIS Helper Task",
        RequiredProductLevel = DTSProductLevel.None)]
    public class ExecuteSQLJobAndWaitControlTask : Task, IDTSComponentPersist
 
    {
        private string _jobName;
        private string _serverName;
 
        /// <summary>
        /// The sql job name
        /// </summary>
        public string JobName
        {
            get { return _jobName; }
            set { _jobName = value; }
        }
 
        /// <summary>
        /// The sql server name
        /// </summary>
        public string ServerName
        {
            get { return _serverName; }
            set { _serverName = value; }
        }
 
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            try
            {
 
                StartJob();
 
                System.Threading.Thread.Sleep(5000);
 
                do
                {
                    System.Threading.Thread.Sleep(5000);
 
 
                } while (IsJobRunning());
 
                if (DidJobSucceed())
                {
                    return DTSExecResult.Success;
                }
                else
                {
                    return DTSExecResult.Failure;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return DTSExecResult.Failure;
            }
        }
 
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
 
            if (string.IsNullOrEmpty(_serverName) || string.IsNullOrEmpty(_jobName))
            {
                componentEvents.FireError(0, "You must specify a JobName and ServerName in the properties", "", "", 0);
                return DTSExecResult.Failure;
            }
            else
            {
                return DTSExecResult.Success;
            }
 
        }
 
        void IDTSComponentPersist.LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
        {
            if (node.Name != "ExecuteSQLJobAndWaitTask")
            {
                throw new Exception(string.Format("Unexpected task element when loading task - {0}.", "ExecuteSQLJobAndWaitTask"));
            }
            else
            {
                this._jobName = node.Attributes.GetNamedItem("JobName").Value;
                this._serverName = node.Attributes.GetNamedItem("ServerName").Value;
            }
        }
 
        void IDTSComponentPersist.SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
        {
 
            XmlElement taskElement = doc.CreateElement(string.Empty, "ExecuteSQLJobAndWaitTask", string.Empty);
 
            XmlAttribute jobNameAttribute = doc.CreateAttribute(string.Empty, "JobName", string.Empty);
            jobNameAttribute.Value = this._jobName.ToString();
            taskElement.Attributes.Append(jobNameAttribute);
 
 
            XmlAttribute serverNameAttribute = doc.CreateAttribute(string.Empty, "ServerName", string.Empty);
            serverNameAttribute.Value = this._serverName.ToString();
            taskElement.Attributes.Append(serverNameAttribute);
 
            doc.AppendChild(taskElement);
        }

And then I have some helper methods, this is where the meat and potatoes are for this task. Now of course I could have the connection string once, etc. Like I said, it was a quick thing :). The heart of it is though, starting the job, checking if it is still running, and then after, if it succeeded. Pretty simple.

 
 
        private bool DidJobSucceed()
        {
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("exec msdb.dbo.sp_help_job @job_name = N'" + JobName + "' ;", dbConn);
            dbConn.Open();
 
            SqlDataReader dr = dbCmd.ExecuteReader();
            dr.Read();
            int status = Convert.ToInt32(dr["last_run_outcome"]);
            dr.Close();
 
            dbConn.Close();
 
            if (status == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
 
        private bool IsJobRunning()
        {
 
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("exec msdb.dbo.sp_help_job @job_name = N'" + JobName + "' ;", dbConn);
            dbConn.Open();
 
            SqlDataReader dr = dbCmd.ExecuteReader();
            dr.Read();
            int status = Convert.ToInt32(dr["current_execution_status"]);
            dr.Close();
 
            dbConn.Close();
 
            if (status == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
 
        }
 
        private void StartJob()
        {
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("EXEC dbo.sp_start_job N'" + JobName + "' ;", dbConn);
            dbConn.Open();
            dbCmd.ExecuteNonQuery();
            dbConn.Close();
        }
   }

Now, to install this you need to register it in the GAC (global assembly cache), and then copy to the DTS/Tasks folder. Depending if you have VS2005 or VS2008 (or both) your gacutil path might be different.

cd\
c:
cd C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin
gacutil /uf "ExecuteSQLJobAndWaitTask"
gacutil /if "C:\Projects\SSISCustomTasks\ExecuteSQLJobAndWait\bin\Debug\ExecuteSQLJobAndWaitTask.dll"
copy "C:\Projects\SSISCustomTasks\ExecuteSQLJobAndWait\bin\Debug\ExecuteSQLJobAndWaitTask.dll" "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks"

I have found once you have done that, you need to actually restart your SSIS service to make it work, but then you can use it in new Visual Studio SSIS packages.

Once you drag it on your package, you can set the JobName and ServerName property (from the properties window – remember, no GUI). and it should run.

Some notes:

If you kill the job, the SSIS task will fail (obviously). If you kill the SSIS package, the job will keep running. Maybe a future enhancement will be to capture the SSIS package fail/cancel and kill the job. Maybe :)

Attached is the source code for the task (Vs2008 C#) http://stevienova.com/ExecuteSQLJobAndWait.rar

This has been testing with BIDS VS2005. I take no responsibility if this blows up your system, computer, server, the world, etc.

Happy ETL’ing!

I need to write to a MySQL database pulling data from a SQL Database, using SSIS 2005. What are the options? Well, the best I could come up with was a Script Component Destination in my DataFlow, this is how I did it:

1) Create a new SSIS Package, throw a DataFlow on the Control Flow, Add your connections (let’s say SourceDB_SQL – your source data, and then DestDB_MySQL – your MySQL destination, it needs to be a ADO.NET Connection, you need to install the MySQL connection – I installed this http://dev.mysql.com/downloads/connector/odbc/5.1.html)

2) In your DataFlow, create your OLEDB Source and get your query returning data, throw a Script Component on the Data Flow and make it a Destination.

3) Open the Script Component, set your input columns up, and then your Connection Manager

4) Open the actual script, and you just have to add a few lines of code:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc
 
Public Class ScriptMain
    Inherits UserComponent
 
    Dim mySQLConn As OdbcConnection
    Dim sqlCmd As OdbcCommand
 
    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        mySQLConn = CType(Me.Connections.MySQLDatabase.AcquireConnection(Nothing), OdbcConnection)
    End Sub
 
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
 
        sqlCmd = New OdbcCommand("INSERT INTO steve_test(ShipTo, YearValue) VALUES(" & Row.ShipTo & ", '" & Row.YearValue & "')", mySQLConn)
        sqlCmd.ExecuteNonQuery()
 
    End Sub
 
    Public Overrides Sub ReleaseConnections()
        Me.Connections.MySQLDatabase.ReleaseConnection(mySQLConn)
    End Sub
End Class

Run it and you are done! Easy. Now you can write data into MySQL using SQL 2005 SSIS (or any ODBC destination if you can get it to work :))

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. :)

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!

Here is a c# code snippet of a function that just outputs to the console, the app I have actually logs the info to a SQL Server database and then I can write reports off that.

     private static void LogSSASInfo(string serverName)
        {
            var server = new Server();
            server.Connect(serverName);
 
            foreach (Database database in server.Databases)
            {
                Console.WriteLine(database.Name + " " + database.LastUpdate + " " + database.EstimatedSize / 1024 + " " + database.CreatedTimestamp);
 
                foreach (Cube cube in database.Cubes)
                {
                    Console.WriteLine("     Cube: " + cube.Name + " " + cube.LastProcessed + " " + cube.LastSchemaUpdate);
 
                    foreach (MeasureGroup measureGroup in cube.MeasureGroups)
                    {
                        Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);
 
                        foreach (Partition partition in measureGroup.Partitions)
                        {
                            Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed);
                        }
                    }
                }
 
                foreach (Dimension dimension in database.Dimensions)
                {
                    Console.WriteLine(" Dimension: " + dimension.Name + " " + dimension.LastProcessed);
                }
 
                Console.WriteLine("");
                Console.WriteLine("------------------------------------------------");
                Console.WriteLine("");
            }
 
            server.Disconnect();           
        }

As you can see, it isn’t the most elegant code in the world, but it works. In order to get this to work in your project, you need to reference the Microsoft.AnalysisServices assembly.

Use your imagination, you could make an app wrap that function above and log info for all the SSAS instances on your network. There have been a few times already in the last year where I have found some cube or measure group not updating correctly and a report like the one I can get now will help dealing with that challenge.

What the link will show you is the differences in commands between Excel 2003 and 2007, so users can learn how to do things with the Ribbon.

Excel 2007 really should be used when hitting SQL 2005+ OLAP Cubes, but companies are reluctant to upgrade because of the “jolt” of learning the Ribbon. Not anymore, with that assistant you can find out how to do anything you could in 2003 (not just with PivotTables – with anything)

What this means, is that there are NO MORE EXCUSES to not upgrade to 2007. Hey, with Office 2010 around the corner – March 2010, you better get ready for it, and there no time like now to upgrade from 2003!

Also, I have piped off my old lifestream using soup.io and I am now using posterous at http://stevenovoselac.com , so most everything non tech/geeky/business intelligence/non blog post will go over there.

In other news, I did pick up a Dell Studio box, 64 bit, 1 TB, 8 GB ram. Running Vista right now, running two VM’s on it, Windows 2008 and a Vista VM, on its own Active Directory. That way, when Win7 comes out I can just keep my VM’s and be good to go. I also got a free Dell Mini 10 netbook with the deal. Not sure I like it. The resolution is just 1 setting to small (or big) for me. I am sure I will find something to do with it though.

I have to move to a new place, or find one anyway, in the next 2 months, so that is thrown into the mix as well, always something to stress ya out, always :)

I download the Adobe Air app pretty quickly and got started, I wanted to see how quickly I could mock up a website I am working on, because there are some new pages and I want just a template, and then be able to quickly mock up what a new page would look like and let the other team members see it without having to code anything. Check this out (this is an example of my site, not the final version):

I did this in less than 5 minutes. I spent more time and refined it to what I wanted, and now I have a template which I can use for new pages, and also a mockup of a report page.

Check out http://mockupstogo.net/?c=1 to get more items, and see examples. You can mock up pretty much any existing site on the web or app, this software is awesome! I wish I would have had this for the last 8 years doing software development.

There was one thing I couldn’t figure out, which was a bar graph but having the bars as columns, but I am guessing I just don’t know how to rotate it, or I need to download some more mock objects.

If you are looking for some software to easily mock up new projects to clients, or to other users on your team, or whoever, check out this tool, you will find that it is easy to use and allows you to quickly mock up and communicate what you are looking for in an end result to your devs as well as other team members (there are some awesome collaboration tools they have baked in – even if someone doesn’t have balsamiq installed, they can import an XML file into a web version to view what it looks like, tweak it, and email it back to you)

Try it out, and happy mocking! :)

Works pretty slick! There goes the need for the custom VBA solution, which is fine by me. Although I am disappointed you still can’t create your own calculated measures. The OLAP Pivot Table Extensions add-in lets you, so I wonder why the built in functionality still doesn’t let you.

Using the AdventureWorks DB in SQL 2005, an example using Manager/Employee:

SELECT DISTINCT mgr.ManagerId, e.EmployeeId
	FROM HumanResources.Employee mgr
	INNER JOIN HumanResources.Employee e ON mgr.ManagerId = e.ManagerId

Results:

But really we want to rollup those employees, ending up with one manager/employee record, ex: 3, [4,9,11,158,263,267,270] … for this, try CROSS APPLY

SELECT DISTINCT ManagerId, Employees = LEFT(emp.list, LEN(emp.list)-1) 
	FROM HumanResources.Employee mgr
	CROSS APPLY
	(
	SELECT CONVERT(VARCHAR(4),EmployeeId) + ',' AS [text()] 
		FROM HumanResources.Employee e
		WHERE mgr.ManagerId = e.ManagerId
 
		ORDER BY EmployeeID
		FOR XML PATH('')
	) emp (list)
WHERE mgr.ManagerId IS NOT NULL

Results:

As you can see from the results, we rolled up our employees into one record per manager, into a comma delimited list. Think of some possibilities of using CROSS APPLY in your apps or stored procs/reports to reduce the number of queries you might have to write, or number of trips to the database you might have to do. Happy T-SQL’ing :)

One easy way to create sub cubes is using this method. If you have one dimension that all your measures relate to (or two, usually they all relate to date), but say one dimension that relates to everything is Location, DimLocation. And you want to create sub cubes based on a set of locations. Well what I did was this.

1) In my Data Source View (.dsv) in my SSAS solution, I replaced all views/tables with Named Queries. (you will see why later)

2) Once I have my “main” cube deployed (and processed if you like, doesn’t matter), I need to create an XMLA to create a sub cube.

3) In the newly created XMLA you have to edit some things

a) I edit the Database name, and Id

b) search for msprop:QueryBuilder="SpecificQueryBuilder" – this is where your named queries for all your tables are. You can go ahead and add a where statement to each one (eg: WHERE LocationId IN (1,2,3))

c) I usually wrap the whole XMLA in a Batch, and then at the bottom after the </Create> I put a process XMLA

  <process>
    <type>ProcessFull</type>
    <Object>
      <DatabaseID>MySubCubeDatabase</DatabaseID>
    </Object>
  </process>

4) Run your XMLA and it should create and process a new SSAS db, creating a pre-sliced version of your original cube.

Now, there are other ways to do all this stuff, one of them being <Filter> in XMLA, but I couldn’t get it to work the way I wanted, that is why I went this route, and it just so happens that I am lucky enough for one dim to relate to all measures :)