greenmtnsun

MCITP BI News; some news for MCTS BI and SQL Server 2008

greenmtnsun — Wed, 17 Oct 2007 17:05:00 GMT

Anticipated recources for the premier Microsoft Business Intelligence certification (MCITP BI) for SQL Server 2005 are fading from Microsoft.

I recently was made aware from Amazon that a book I pre-ordered titled "MCITP Self-Paced Training Kit (Exam 70-446): Designing and Developing a Business Intelligence Infrastructure by Using Microsoft SQL Server(TM) 2005 (Mcitp Self Paced Training Kit)" was cancled.

I spoke with someone* from MSPRESS and found out the following:

"We postponed the 70-446 Training Kit, but plan to get it going again for the SQL Server 2008 release. I can only give a rough estimate on availability, probably Summer 2008. "

In a later e-mail the person I spoke with shared that they agreed that "A Training Kit would be the optimum preparation for the 70-446 exam. I’m sorry we don’t have one available now, but we’re planning an edition for the upcoming SQL Server 2008 release."

So... If you're interested in the MCITP BI and want to obtain that before next Summer, they suggest the following books:

SQL Server 2005 Analysis Services Step by Step
SQL Server 2005 Reporting Services Step by Step
SQL Server 2005 Integration Services Step by Step
And use this to prepare: http://www.microsoft.com/learning/exams/70-446.mspx

One thing on that website that is innacurate is that Measure Up and Self Test Software companies do NOT have the 70-446 exam preps for this cert and from what I can tell, they will not. The only company that seems to have a test prep kit is Transcender; and I doubt Microsoft recommends them.

I also heard that "There’s a PerformancePoint 2007 TS exam planned for later this year/early next, but no current plan to tie it in with the SQL Server 2008 MCITP BI certification."

Regarding the MCTS BI exam, my source shared "Currently, the SQL Server 2008 BI exams will include a completely new TS exam (replacing 70-445) and a refreshed version of the 70-446 exam. But there’s been nothing official yet and plans can change."

Interesting news, huh?

Keith

_________________________________________________________________________________________________

* I can share the name of the person individually off the record with you if asked, but I'm not sure its wise to share the persons name publicly.

SSIS conditional Split

greenmtnsun — Mon, 24 Sep 2007 21:20:00 GMT

I needed to figure out how to get SSIS to consume a file that had lines of data with attributes of both "apples" and "oranges". No matter where I looked online, it was as if SSIS could not take in a file that had different types of data on each line and then throw that data into a database. The situation looked bleak, until I found a little item in my toolbox inside a data flow called "Conditional Split".

Conditional Split is almost like a little tool that makes your flat file like XML in that you can send data from the source to many tables. When you consume an XML file in SSIS, you could have many tables that could flow from that source. The same is true when you use Conditional Split; you tell SSIS when a line is a different row type and BAMM, you are on your way to multiple tables of data getting inserted into your database.

For me, the trick to all of this wasn't in how I accomplished this, but rather how to know what types of tools you need to use in SSIS to do what you need to do.

I have my Data Flow task.

Inside that I have my flat file Source.

That component points to a "Conditional Split" component.

Inside of that component I simply created multiple conditions that meant that if a line started like X, then output that line to a separate flow into a table. In my case, I said substring(Field_name,1,5)=="Batch" and called that Output Name "Batch_Details" table. I created other lines to, like substring(Field_name,1,3)=="Act" with an Output name of "Account Details". You can create as many types of output lines as you might need, although I don't know what the technical limit is, I only needed 10.

That component then pointed to a derived columns component. In my case, I had a fixed length file so I created a bunch of columns that were created by substring commands.

That component pointed to a OLE DB Destination.

That was it. It turned out to be pretty simple; if I could only figure out how SSIS could perform the actions. I remain disappointed that there weren't many posts, FAQ's or much of anything out on the net to help reveal how to perform these actions.

Keith

HTTP Authenticatoin Download of File in SSIS to XML file

greenmtnsun — Fri, 07 Sep 2007 18:01:00 GMT

Recently I posted a script that allows you to download a CSV file to SSIS through HTTP Authentication, today, I'm posting a slightly different script that uses some XML references in order to download an XML file. You can probably use the other way that I wrote for the CSV file, but since I had both, I thought I'd blog them here for posterity sake.

Create a script task on the control flow tab.
Populate your URL in a prior step to a variable. I used a SQL Task to format a complicated file name into the URL.
Make sure your script task passes the URL in the ReadOnlyVariables to the script.
Use this script below. Correct the place for your UNC path for your output file, user id, password.
Consume your data in a data flow task.

I hope this helps people; it took me a while to figure it out.

Keith

I used this VB.NET Script below:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Imports System.Collections.Generic
Imports System.Text
Imports System.Xml

Public Class ScriptMain
    Public Sub Main()
        Try
            Dim restURL As New StringBuilder()
            Dim restRequest As HttpWebRequest
            Dim restResponse As HttpWebResponse
            Dim xDoc As New XmlDocument()

            restURL.AppendFormat(Dts.Variables("URL").Value.ToString())
            restRequest = DirectCast(WebRequest.Create(restURL.ToString()), HttpWebRequest)

            ' the key line. This adds the base64-encoded authentication information to the request header
            restRequest.Headers.Add("Authorization", "Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("USER_ID_GOES_HERE:PASSWORD_GOES_HERE")))

            restResponse = DirectCast(restRequest.GetResponse(), HttpWebResponse)

            xDoc.Load(restResponse.GetResponseStream())
' If you wanted to, you could change this next line to interact with a variable, just be sure to pass it to the script.
            xDoc.Save("UNC_PATH_AND_FILE_NAME_GO_HERE")

            xDoc.Save(Console.Out)

            Dts.TaskResult = Dts.Results.Success

        Catch webEx As WebException

            Dim [error] As New StringBuilder()

            'catch protocol errors

            If webEx.Status = WebExceptionStatus.ProtocolError Then
                [error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)
                [error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)

                ' post the error message we got back. This is the old error catch code that might work better with SSIS.
                Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)

                Dts.TaskResult = Dts.Results.Failure
            End If
        End Try

    End Sub

End Class

HTTP Authenticatoin Download of File in SSIS to CSV file

greenmtnsun — Thu, 06 Sep 2007 17:05:00 GMT

I recently needed to perform a download of a CSV file with HTTP Authentication in SSIS to a CSV file. Its possible, but I needed a script to do it. I couldn't use an HTTP connection because nothing in SSIS would consume it. Nothing, including the File System, WMI and execute process tasks would work in the control flow and none of the data flow sources would consume the file. I was forced to use a script since the company I was downloading from had no FTP and no web service for this file.

Create a script task on the control flow tab.
Populate your URL in a prior step to a variable. I used a SQL Task to format a complicated file name into the URL.
Make sure your script task passes the URL in the ReadOnlyVariables to the script.
Use this script below. Correct the place for your UNC path for your output file, user id, password.
Consume your data in a data flow task.

I hope this helps people; it took me a while to figure it out.

Keith

p.s. Thanks to my co-worker Kirk Palmer for assistance with the VB.NET script.

I used this VB.NET Script below:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Imports System.Collections.Generic
Imports System.Text

Public Class ScriptMain
    Public Sub Main()
        Try
            Dim restURL As New StringBuilder()
            Dim restRequest As HttpWebRequest
            Dim restResponse As HttpWebResponse
            Dim tDoc As StreamReader
            ' modify the date, report name, merchant ID, and processing environment (production/test) as needed
            restURL.AppendFormat(Dts.Variables("URL").Value.ToString())
            restRequest = DirectCast(WebRequest.Create(restURL.ToString()), HttpWebRequest)

            ' the key line. This adds the base64-encoded authentication information to the request header
            restRequest.Headers.Add("Authorization", "Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("USER ID:PASSWORD")))

            restResponse = DirectCast(restRequest.GetResponse(), HttpWebResponse)
            tDoc = New StreamReader(restResponse.GetResponseStream())

            Dim f As File
            f.WriteAllText("UNC PATH FOR OUTPUT FILE", tDoc.ReadToEnd)

            Dts.TaskResult = Dts.Results.Success

        Catch webEx As WebException

            Dim [error] As New StringBuilder()

            'catch protocol errors

            If webEx.Status = WebExceptionStatus.ProtocolError Then
                [error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)
                [error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)

                ' post the error message we got back. This is the old error catch code that might work better with SSIS.
                Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)

                Dts.TaskResult = Dts.Results.Failure
            End If
        End Try

    End Sub

End Class

Vista and SSRS

greenmtnsun — Tue, 28 Aug 2007 17:18:00 GMT

Did you know that Microsofts new operating system will only allow you to install SSRS's full features on its Business and Ultimate editions? Yep. Vista does not allow users access to a little itsy feature in IIS called Windows Authentification. So while your Vista Premium machine might have IIS, it has this little tiny thing excluded from it. Microsofts explaination is that IIS 7 on Vista Premium is for the Hobyist web developer.

I found out the hard way; I asked my Dell rep when I baught my PC if the Vista Premium had IIS, and he said "YES!" Too bad he didn't know that Microsoft decided to exclude Windows Authentification from some versions. So much for the Vista Premium purchase; and Dell didn't help me at all upgrade to Ultimate. I had to purchase a Vista Ultimate upgrade on my own from some online retailer. Yikes was my wife anoyed at the cost of my being an IT geek. On the bright side I can now play Texas Hold Em. (Deep sarcasm.)

Let this be a warning to those of you who want to develop and install all of SSRS from home on your own PC.

Here are a couple of links that I found on the subject:

http://www.iis.net/articles/view.aspx/IIS7/Deploy-an-IIS7-Server/Installing-IIS7/IIS7-Features-and-Windows-Vista-Editions

http://support.microsoft.com/default.aspx/kb/920201/en-us

Simple Stored Procedure to return data to a SSRS Report from Oracle

greenmtnsun — Mon, 27 Aug 2007 22:09:00 GMT

If you are like me, SQL Server is a breeze to write a Stored Procedure against and Oracle isn't as intuitive. I had a report that simply needed to do the following:

1. Run a query in Oracle.

2. Return the data to Reporting Services so that it could report on it.

3. I couldn't link through SQL Server.

Its really simple code, but different than the way we do it on the SQL Server side. In SQL Server you just do a Select at the end of your SP and it gets presented to Reporting Services, but Oracle needs to use a Cursor. I scoured the internet real quick to find an example SP like I can find for all kinds of things in regards to SQL Server, but not Oracle.

So, eventually I figured out I needed to pass SSRS a SYS_REFCURSOR and then figured out this simple little SP.

Anyway, if this saves someone 20 minutes of googling, I thought I'd post it. There is nothing revolutionary about it.

CREATE OR REPLACE PROCEDURE SCHEMA_NAME.USP_RPT_SP_NAME (PARAM1 IN DATE, PARAM2 IN DATE, p_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset1 FOR
SELECT * from TABLE_NAME
WHERE ADDDTTM between PARAM1 and PARAM2;
END USP_RPT_SP_NAME;
/

Dynamic ORACLE SP for calling a linked server database environment based on a parameter.

greenmtnsun — Mon, 27 Aug 2007 21:41:00 GMT

I haven't posted anything here yet on this blog, so I thought I'd pull some lesson I learned a while back and use it as a post. If it helps you; great, if not, no worries. The problem I tried to solve involved 5 or 6 Oracle environments that need to have...(read more)