Solved

SSIS Script error: Name 'Dts' is not declared

Posted on 2011-03-02
5
4,919 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 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