Recently I had a client that wanted to load an Excel File from a SQL query from an SSIS package. Since the Data Flows in SSIS are pretty rigid in terms of columns and data types it can not just add or delete columns on the fly. So I developed a script task in a package to accept a SQL command and dynamically build the excel file. 

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

Imports System
Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Threading
Imports Microsoft.Office.Interop.Owc11

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()
        'Load Package variables
        Dim FileName As String = Dts.Variables("strDestinationFileName").Value
        Dim connectString As String = Dts.Variables("strSourceConnection").Value
        Dim sqlQuery As String = Dts.Variables("strExportQuery").Value
        Dim worksheet As String = Dts.Variables("strDestinationWorksheet").Value

        GenerateExcelSheet(connectString, sqlQuery, FileName, worksheet)

        Dts.TaskResult = ScriptResults.Success
    End Sub

    Public Sub GenerateExcelSheet(ByVal ConnectString As String, ByVal sqlQuery As String, ByVal FileName As String, ByVal worksheet As String)
        Try
            Using sqlCon As SqlConnection = GetSqlConnection(ConnectString) 'get the SqlConnection
                Dim sqlCommand As New SqlCommand(sqlQuery, sqlCon) 'create the sql command
                Dim dr As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)  ' populate DataReader
                Dim rowCount As Integer = 1 ' reset row counter

                Dim spread As Spreadsheet = New SpreadsheetClass() ' create an instance of SpreadSheet Web Control

                spread.ActiveSheet.Name = worksheet 'Change the Sheet name

                For colCount As Integer = 0 To dr.FieldCount - 1 ' write out field headers
                    spread.Cells(rowCount, colCount + 1) = dr.GetName(colCount)
                Next

                While dr.Read() ' write out the actual data by looping thru the dataReader
                    rowCount += 1
                    For colCount As Integer = 0 To dr.FieldCount - 1
                        spread.Cells(rowCount, colCount + 1) = dr.GetValue(colCount).ToString().Replace(vbCr, "").Replace(vbLf, "") ' filter out any \r & \n in the data 
                    Next
                End While

                spread.Export(FileName, SheetExportActionEnum.ssExportActionNone) ' generate the XLS file

                spread = Nothing ' de-reference the SpreadSheet Control

            End Using
        Catch ex As Exception
            MsgBox(ex.ToString)
            Throw ex
        End Try

    End Sub

    Private Function GetSqlConnection(ByVal ConnectString As String) As SqlConnection
        Dim sqlCon As New SqlConnection(ConnectString)
        Try
            ' make sure the time-out value specified is acceptable
            If Not (sqlCon.ConnectionTimeout = 0) OrElse (sqlCon.ConnectionTimeout > 60) Then
                sqlCon.Open()
                Return sqlCon
            Else
                Throw New Exception("Invalid Connection Time Out Value Specified")
            End If
        Catch ex As Exception
            'MsgBox(ex.ToString)
            Throw ex
        End Try
    End Function
End Class

My blog and all of the other pragmatic works blogs have moved to http://blogs.pragmaticworks.com/. My blog is at http://blogs.pragmaticworks.com/mike_davis/.

If you have a Select SQL command in an SSIS package that has a parameter, you may get no value from the query. This will cause the Execute SQL task to fail if it is looking for a value to pass into the result set variable. To get around this use an If Exist command in the Select SQL statement.

Example:

You have the following statement in your Execute SQL Task in your package that fails when the product ID is not in the table:

Select Style
From Production.product
Where ProductID = ?

If the ProductID does not exist in the table there will be no results and his will cause an error. To stop this problem use:

IF not Exists(
Select Style
From Production.product
Where ProductID = ?)
begin
    Select ''
end
Else
Select Style
From Production.product
Where ProductID = ?

This will always return either the style or an empty string. You could also use this on a numeric column and return a numeric value like 0 when it does not exist. 

I had an interesting question from a student in one of my SSIS classes. If you are calling a package from the DTexec command using the set command to pass in a variable value and the package has a configuration file setting the variable value, which one wins out?

I set up a package with a variable named "Test" and the value in the package set to "Package". The value of the configuration file passing in the value to the test variable was set to "Config". I then called the package with a DTexec command line and used the set command to change the variable of the test variable to "DTexec". I had a script task in the package pop up the value of the variable. The message box read "Config".

So the configuration file wins out versus the DTexec set command.

Then the question becomes, how do you override this? Instead of using the set command for the variable, use the conf command to set the configuration file to a different configuration file. Save the value you need in the configuration file. The package will use the new configuration file.

"Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report, the link is no longer valid"

This is an error that is easily avoided. When deploying a report to the report server we need to ensure that the data source have the credentials stored. Simply click on the data sources link in the report server web interface. Then select the data source that is being used for the report. Change the data source to stored credentials and enter in the user name and password of the user name that will be used to run the subscription. Usually this is a user name just used for subscriptions and not an actual person. The password should be set to not expire also. If the password for the user expires then your subscriptions will stop running.

If you are running a report from an OLAP cube in analysis services you will need to ensure the credentials are stored there also. The only way to connect to analysis services is with windows credentials so you will need to set up the stored credentials as above and also check the box for Use as Windows credentials. This will allow subscriptions to be created for reports pointing to analysis services.

Let me know if you know of any other issues with reports or integrated services and I will try to post the answers on my blog.

If you need to loop through and XML file, or just want to have a loop in an SSIS package loop through a list you have created using a for each nodelist is the task to use.

First we create a for each loop and set the collection to node list enumerator. Then we set the document source type to file connection and the document source to our XML file. I suggest using a file instead of direct XML because this allows us to update the package externally and prevents us from having to redeploy the package. I have a simple XML file on my C drive I have selected.

Set the enumeration type to NodeText. Set the OuterXpathStringSourceType to DirectInput and the OuterXpathString to the node we need to find. We could set this to an external file also which would allows us to change this outside the package, for this example we will set it to DiectInput. We also need to place “//” in front of the node value. Now the “for each loop” will run for each node in the XML file that matches our Outerxpathstring.

We can then set a variable to capture the node information and use it in our package.

It this example we have a list of databases that we could use to dynamically set the connections used in our data flows.

SSIS For Each ADO Enumerator Loop

If you need an SSIS package to run an execute SQL task with a where clause and you need to have a parameter in the where clause traverse through a list of values in a table.

In this example we will find the types of Customers in the Adventure works Customer Table and run a “for each loop” with the where clause targeting each distinct customer type in each iteration of the loop.

The first task we will create is an execute SQL task that will find the list of account types. The sql statement in this task will look like this: “SELECT distinct [CustomerType]  FROM [AdventureWorks].[Sales].[Customer]”. We will save this in and object variable called strAccountList with a result set of “full result set”. This is basically a table with one column listing all the account types once.

Now we will create a for each loop and set the collection to “for each ado enumerator”. We will select the strAccountList in the variable drop down. We can then set a new variable in the loop and call it strCurrentAccountType. This variable can be used throughout the foreach ADO enumerator loop.

 Now we can create another execute sql task in the loop with a where clause like this: “SELECT [AccountNumber]  FROM [AdventureWorks].[Sales].[Customer] where [CustomerType] = ?”. The question mark is the parameter. We map the parameter to the stCurrentAccountType variable and now the SSIS package will execute the sql statement for each distinct account type in the table.

Now we get a list of account numbers broken up by customer types that we can pass to a variable and use in our loop.

I was then able to set the UITypeName to SendMailAdvancedNS.SendMailAdvancedNS.SendMailAdvancedUI

In my last Blog entry I wrote about how to clear all text boxes in a windows form. But what if that form has tabs? Then you will need to set up a loop inside of loop inside of a loop. I know it seems confusing, but it is much better than the alternative of coding every text box name on every tab page into a reset or clear button. Especially if you have hundreds of text boxes. Here is the code to do it.

        Dim x As Integer
        For x = 0 To Me.Controls.Count - 1

            If TypeOf Me.Controls.Item(x) Is TextBox Then
                Me.Controls.Item(x).Text = ""
            End If

            If TypeOf Me.Controls.Item(x) Is TabControl Then

                Dim tabControl As TabControl = Me.Controls.Item(x)
                Dim z As Integer

                For z = 0 To tabControl.TabCount() - 1

                    Dim TabPage As TabPage = tabControl.TabPages(z)
                    Dim y As Integer

                    For y = 0 To TabPage.Controls.Count - 1
                        If TypeOf TabPage.Controls.Item(y) Is TextBox Then
                            TabPage.Controls.Item(y).Text = ""
                        End If
                    Next

                Next

            End If

        Next

We start out with a loop on the current page controls and clearing any text boxes there, then we detect if the control is a tab control. Then we start a loop inside of that for each tab page. Then a loop in that for each control on the tab page.  I created variables with the name of the control to make it easier to read. You can leave this out and just use the name in the precdeing loop but it can get ugly, like:  Me.Controls.Item(x).TabPages(z).Controls.Item(y).Text = "". This would be the end results if we did not you the variables.

 You can use this same concept with any container on a form.

When you have a reset button on a vb form and you want that button to clear all of the text box fields, you have two options. Write code for each field to clear them or loop through each control on the form.

Here is an example of clearing each field individually:

Private Sub resetForm()

       ServerName.text = ""
       PortNumber.text = ""
      and so on ....

End Sub

As you can see this give you complete control of each control individually. But if you have dozens of fields this can be a headache. Also, maintaining this becomes a hassle. Each time you add a control you have to update the resetform method.

Here is the loop through example:

Private Sub resetForm()

    'Set an integer to loop through the controls
    Dim x As Integer
    ' Set the loop for one less than the count because the fields start their index at 0
    For x = 0 To Me.Controls.Count - 1

       'if it is a text box then clear it 
        If TypeOf Me.Controls.Item(x) Is TextBox Then
            Me.Controls.Item(x).Text = ""
        End If

    Next

End Sub

As you can see, this method never needs to be updated. You can add or remove controls to your form and the method with react accordingly.

You could also do the same thing for radio buttons, check boxes or any other editable field. You just need a case for each type of control.

 Hope this helps someone.

I will be speaking at the SQL server 2008 Launch in Jacksonville, FL.

My sessions are:

Converting DTS to SSIS : 11-Noon

Reporting Services : 1-2pm

http://www.sqlsaturday.com/eventhome.aspx?eventid=10

Join us on Friday, September 12, 2008, for a one day look at the new features of SQL Server 2008 as we celebrate the launch of the latest version. Our event will be located at the Modis Building, 1 Independent Dr, Jacksonville, FL. We'll have 15 sessions on SQL Server that will be announced in the new few days. There is a fee for parking for the event that will range from $5-10 depending on the parking location you select. Breakfast and lunch will be provided by our event sponsors. This event is being coordinated by members of the Jacksonville SQL Server Users Group (www.jssug.com).

We had a customer that had time saved as a string and needed it converted to a date time field. They wanted to combine it with another date field. Here is a simple script to convert in situaions like this in an SSIS transformaion script. By the way some of the time strings were null and some had bogus times in them. After writing it I should have added a check to make sure the time is between 0000 and 2400.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim NewDateTime As Date
        Dim HourStr As String
        Dim MinuteStr As String

        'validate time is not null
        If Not (Row.NATIME_IsNull) Then

            'Validate  time is a 4 digit number
            If IsNumeric(Row.NATIME) And Row.NATIME.ToString.Length = 4 Then

                ' break time into hours and minutes
                HourStr = Row.NATIME.Substring(0, 2)
                MinuteStr = Row.NATIME.Substring(2, 2)

            Else ' iff time is not a 4 digit number
                HourStr = "00"
                MinuteStr = "00"
            End If

        Else ' if time is null

            HourStr = "00"
            MinuteStr = "00"

        End If

        'combine date and time
        NewDateTime = CDate(CStr(DateSerial(Year(Row.Date), Month(Row.Date), DatePart("d", Row.Date))) + " " + _
           CStr(TimeSerial(CInt(HourStr), CInt(MinuteStr), 0)))

        'output row
        Row.NewDateTime = NewDateTime

    End Sub

It can be a pain to launch an SSIS package remotely. So I developed a vb.net standalone application to remotely launch a package. It does require the host computer to have SSIS installed. This is just a beginning program. I am going to be adding other features like variables and config files. Eventually it will allow you to run the package remotely where the package will run on the remote machine. But this will probably require creating a web service.  

Please leave comments, this is my first blog and my first program I have posted online.

Thanks

Mike Davis