Solved

SSIS Script error: Name 'Dts' is not declared

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

948 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

20 Experts available now in Live!

Get 1:1 Help Now