Old ladyHow Big Is Your Package?
Size up and convert your DTS package to SSIS. If you have the biggest DTS package, win a gift card and certificate of a world record package size.  
Mike Davis
in

Pragmatic Works

Mike Davis

  • Dynamically Creating Excel from SQL

    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

  • Blogs Moved

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

  • SSIS Execute SQL Task Fails with No Data

    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. 

  • SSIS Configuration File vs Set Command from DTexec

    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.

  • Reporting Services, Subscriptions cannot be created because the credentials are not stored

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

  • SSIS For Each Node List Enumerator

    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.

    image

    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.

    image

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

    image

    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

    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.

    ADO

    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

    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" column
    In 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

    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

    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.

  • Clear or Reset all textbox controls on a VB .net 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.

  • Sql Server 2008 Launch Jacksonville

    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

    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

    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
More Posts
Copyright Pragmatic Works
Powered by Community Server (Non-Commercial Edition), by Telligent Systems