Solved

SSIS Script error: Name 'Dts' is not declared

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retrieve a Registry value using SCCM 2012. 2 45
SQL Server for XML PATH giving wrong results. 6 59
how to resize column length with primary ket 4 33
SQL query 45 41
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

734 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