Mike Davis

SSIS For Each Node List Enumerator

Mike Davis — Fri, 19 Dec 2008 20:11:18 GMT

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

Mike Davis — Fri, 19 Dec 2008 16:09:00 GMT

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.

Find the Fully qualified NameSpace of SSIS DLL

Mike Davis — Mon, 01 Dec 2008 13:38:00 GMT

When writing a custom SSIS task the best practice is to create the UI separate from the control. This will allow the task to run faster during package execution. One of the problems people have when creating a custom SSIS task is, knowing the fully qualified namespace of the task to enter into the UITypeName. Example:Namespace SendMailAdvancedNS
    <DtsTask(DisplayName:="Send Mail Advanced", _
    IconResource:="SendMailAdvancedNS.Letter.ico", _
    Description:="Send Mail with Advanced Features", _
    UITypeName:="SendMailAdvancedNS.SendMailAdvancedNS.SendMailAdvancedUI,SendMailAdvancedUI, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=e69ec455025fad04", _
    TaskContact:="Support PragmaticWorks.com (c) 2008 ;http://www.pragmaticworks.com")> _
    Public NotInheritable Class SendMailAdvanced The UITypeName can be hard to know if you have little experience creating custom SSIS task. The easiest way to find it is to look in the list of task in business intelligence development studio after you have installed the dll to the GAC.1. Start Business Intelligence Development Studio(BIDS)
2. Open an existing Integrated Services Project or create a blank one
3. Right click on the tool box and select choose item...
4. Select the SSIS control flow Items Tab
5. Scroll down to find the DLL you installed in the GAC, the fully qualified Namespace will be listed in the "Type Name" columnIn the case above it was SendMailAdvancedNS.SendMailAdvancedNS.SendMailAdvanced,

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

Text Qualifier in Column Names

Mike Davis — Fri, 31 Oct 2008 12:46:00 GMT

If you have a flat file with quotes around the data as text qualifiers you may have seen the issue where the headers has the quotes even after you put the quote in as the text qualifier. This is a simple issue to resolve. If you check the box “Columns names in the first data row” SSIS sets the first row as the metadata for the column names.

The problem comes when the data has a text qualifier. The text qualifiers end up in the column names.

At this point some people will and check the “Columns names in the first data row” first, and then put in a text qualifier. The problem with that is the text qualifiers end up in the metadata.

To fix this, always enter the text qualifier first then check the box for the column headers.

This will tell SSIS that the text qualifiers are in the first row before it sets that row as the metadata.

Clear all text boxes with a loop in Tabs

Mike Davis — Thu, 25 Sep 2008 16:06:00 GMT

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

End If

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.

Clear or Reset all textbox controls on a VB .net Form

Mike Davis — Fri, 19 Sep 2008 13:36:00 GMT

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

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.

Sql Server 2008 Launch Jacksonville

Mike Davis — Thu, 11 Sep 2008 21:17:00 GMT

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).

Converting a string to a time and date

Mike Davis — Thu, 04 Sep 2008 12:13:00 GMT

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

Launch Remote SSIS Package Programmatically

Mike Davis — Wed, 20 Aug 2008 13:15:00 GMT

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

The source code is attached