Solved

SSIS Script error: Name 'Dts' is not declared

Posted on 2011-03-02
5
4,677 Views
Last Modified: 2012-05-11
I'm trying to add code which uses the Dts collection/object/name such as  "Dts.Variables("FFIsEmpty").Value = True" and " Dts.TaskResult = ScriptResults.Success" but I get the error "Name Dts is not declared" and "Name ScriptResults is not declared."

I've followed the Microsoft's KB article 955525 which says the resolution is to make sure the code is in ScriptMain.  

I've tried the code for the Sub Main in different parts of the code, including inside the Input0_ProcessInputRow, but no luck.

The code below inside Input0_ProcessInputRow works properly.   It's just the Sub Main with the references to Dts that do not.  Thank you!
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper


<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent
    Public Sub Main()

        Dim nonDataRows As Integer = _
            DirectCast(Dts.Variables("FFNonDataRows").Value, Integer)
        Dim ffConnection As String = _
            DirectCast(Dts.Connections("EmptyFlatFileTest").AcquireConnection(Nothing), _
            String)
        Dim flatFileInfo As New FileInfo(ffConnection)
        ' If file size is 0 bytes, flat file does not contain data.
        Dim fileSize As Long = flatFileInfo.Length
        If fileSize > 0 Then
            Dim lineCount As Integer = 0
            Dim line As String
            Dim fsFlatFile As New StreamReader(ffConnection)
            Do Until fsFlatFile.EndOfStream
                line = fsFlatFile.ReadLine
                lineCount += 1
                ' If line count > expected number of non-data rows,
                '  flat file contains data (default value).
                If lineCount > nonDataRows Then
                    Exit Do
                End If
                ' If line count <= expected number of non-data rows,
                '  flat file does not contain data.
                If lineCount <= nonDataRows Then
                    Dts.Variables("FFIsEmpty").Value = True
                End If
            Loop
        Else
            Dts.Variables("FFIsEmpty").Value = True
        End If

        Dim fireAgain As Boolean = False
        Dts.Events.FireInformation(0, "Script Task", _
            String.Format("{0}: {1}", ffConnection, _
            Dts.Variables("FFIsEmpty").Value.ToString), _
            String.Empty, 0, fireAgain)

        Dts.TaskResult = ScriptResults.Success

    End Sub

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        '
        ' Add your code here for preprocessing or remove if not needed
        '

    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        '
        ' Add your code here for postprocessing or remove if not needed
        ' You can set read/write variables here, for example:
        ' Me.Variables.MyIntVar = 100
        '
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        '
        Dim arr() As String = Row.EventTypeCode.Split(","c)
        Dim iReadingCount As Integer = 1
        'we loop around on the same row so long as there are
        'delimited values to be extracted
        For i As Integer = 0 To arr.Length - 1
            With Output0Buffer
                'add a row to the output buffer

                'Set the values of each of our output buffer columns
                If Not Row.PatientFullName_IsNull Then
                    .AddRow()
                    .PatientFullName = Row.PatientFullName
                    '.InternalMsgID = Row.InternalMsgID
                    '.EventTypeCode = Left(Row.EventTypeCode, 2)

                    '.ReadingID = iReadingCount
                    '.DateOfReading = System.DateTime.Now
                    '.Reading = CType(arr(i), Integer)
                    'Increase the reading count by 1
                    iReadingCount += 1
                End If
            End With
        Next

    End Sub

    Public Overrides Sub CreateNewOutputRows()
        '
        ' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
        ' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        '
    End Sub

End Class

Open in new window

0
Comment
Question by:richxyz
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35023557
Try adding : Imports Microsoft.SqlServer
0
 

Author Comment

by:richxyz
ID: 35023602
Yes, I had tried that too (I forgot to mention.)  When I add Imports Microsoft.SqlServer it changes the errors to 'Variables is not a member of Dts.' and 'Connections is not a member of Dts.'
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 250 total points
ID: 35023748
Apparently the correct namespace is Microsoft.SqlServer.ManagedDTS but you will need to add a reference to the ManagedDTS.dll to your project.
0
 

Author Comment

by:richxyz
ID: 35027134
Yes, I had checked that before too, and didn't mention it in my initial posting.  Version 10.0.0.0 is listed in my .NET references already.  (Runtime v.2.0.50727)

Any other ideas?
0
 

Author Comment

by:richxyz
ID: 35040847
I figure it out with the help of a collegue

I chanegd lines 19-23 in the code above to the following.  
Dim var As Dts.Runtime.Variables
Dim con As Dts.Runtime.Connections

Dim nonDataRows As Integer = _
   DirectCast(var("FFNonDataRows").Value, Integer)
Dim ffConnection As String = _
   DirectCast(con("EmptyFlatFileTest").AcquireConnection(Nothing), _ String)

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now