Solved

SSIS Script error: Name 'Dts' is not declared

Posted on 2011-03-02
5
4,734 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

806 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