Warning: 0x7 at : Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime in a SSIS package

If you get a warning like:

Warning: 0x7 at Filesysteem opschoning: Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime.

when trying to debug a SSIS package, change the project property [Run64BitRuntime] to False:

image

Microsoft SQL Server Integration Services 2008 – Data Profiling, automatically analyze you’re data

On channel 9 there is a video on Microsoft SQL Server Integration Services 2008 and data profiling. SSIS 2008 contains a data profiling task, it allows you to automatically analyze you’re data:

> Is the data in this column unique?
> What is the percentage of null values in this column?
> Create regular expressions for new validation rules in de UI
> Column statistics on numeric  columns, like min value, max value, standard deviation
> Number of distinct values and what these values are

Link
http://channel9.msdn.com/shows/toolshed/ToolShed-Tooltip-20-SSIS-Data-Profiling-saves-your-eyeballs/

Database connectionstring error in Microsoft SQL Server Integration Services (SSIS)

When you use the oledb destination in a SSIS package, you must use the oledb connectionstring syntax, see http://www.connectionstrings.com/sql-server-2008 for more information.
If you use the connectionstring: Data Source=Server1\InstanceName1;Initial Catalog=Database1;Provider=SQLNCLI10.1;Trusted_Connection=True;  you wil get the error: "Invalid value specified for connection string attribute” ‘Trusted_Connection’", because the syntax should be Data Source=Server1\InstanceName1;Initial Catalog=Database1;Provider=SQLNCLI10.1;Trusted_Connection=yes

Workaround
After this error I decided to use the syntax Data Source=Server1\InstanceName1;Initial Catalog=Database1;Provider=SQLNCLI10.1;Integrated Security=SSPI; this syntax will work on sql, oldb and odbc connections.

 

Error Message
Executed as user: Domain1\User1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:09:46 PM  Error: 2009-07-30 15:09:46.63     Code: 0xC0202009     Source: ControleerProces Connection manager "Database1"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid value specified for connection string attribute ‘Trusted_Connection’".  End Error  Error: 2009-07-30 15:09:46.63     Code: 0xC0202009     Source: ControleerProces Connection manager "Database1"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid value specified for connection string attribute ‘Trusted_Connection’".  End Error  Error: 2009-07-30 15:09:46.65     Code: 0xC0202009     Source: ControleerProces Connection manager "Database1"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid value specified for connection string attribute ‘Trusted_Connection’".  End Error  Error: 2009-07-30 15:09:46.65     Code: 0xC020801C     Source: Controleer nieuwe foutmeldingen Ophalen foutmeldingen [31]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Database1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2009-07-30 15:09:46.65     Code: 0xC0047017     Source: Controleer nieuwe foutmeldingen SSIS.Pipeline     Description: component "Ophalen foutmeldingen" (31) failed validation and returned error code 0xC020801C.  End Error  Error: 2009-07-30 15:09:46.65     Code: 0xC004700C     Source: Controleer nieuwe foutmeldingen SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-07-30 15:09:46.65     Code: 0xC0024107     Source: Controleer nieuwe foutmeldingen      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:09:46 PM  Finished: 3:09:46 PM  Elapsed:  0.297 seconds.  The package execution failed.  The step failed.

Microsoft SQL Server Integration Services (SSIS) 2008 and logging with the SSIS log provider for SQL Server

If you use the SSIS log provider for SQL Server in Microsoft SQL Server Integration Services (SSIS) 2008, the provider will create a table ‘sysssislog’ in the destination database. The table will be created with schema name. If the SSIS package is run under the “dbo” the table will be created as “dbo.sysssislog”, but if the package is run under a database user which is not “dbo” of the database the tabled will be created like: “Domain1\User2.sysssislog”. The system stored procedures that are used for logging, aspect de table schema name to be “dbo” en not “Domain1\User2”, so it will fail with error, see below.

We use a sql server agent job to run a SSIS package. The job step is set to “Run as” a proxy account, which is authorized for the subsystem “SQL Server Integration Services Package”. The proxy is coupled to a credential, the credential is coupled to a windows account that has “Run as a Job” permissions and is member of the msdb database role “’SQLAgentUserRole”. The account that runs the SSIS package is not “dbo”, because the database is created by another account.

 

Workarround
Because we want the database to be installed by user “Domain1\User1” and the “sql server agent job step that executes the SSIS package” to be run as user “Domain1\User2”, we can’t let the SSIS package create the table automatically. We create the table during database installation by the “Domain1\User1” account. The table is then created as “dbo.sysssislog”.

Table create script

create table [sysssislog](
[id] [int] identity(1,1) not null,
[event] [sysname] not null,
[computer] [nvarchar](128) not null,
[operator] [nvarchar](128) not null,
[source] [nvarchar](1024) not null,
[sourceid] [uniqueidentifier] not null,
[executionid] [uniqueidentifier] not null,
[starttime] [datetime] not null,
[endtime] [datetime] not null,
[datacode] [int] not null,
[databytes] [image] null,
[message] [nvarchar](2048) not null,
primary key clustered
(
[id] asc
)with (PAD_INDEX  = off, STATISTICS_NORECOMPUTE  = off, IGNORE_DUP_KEY = off, ALLOW_ROW_LOCKS  = on, ALLOW_PAGE_LOCKS  = on) on [PRIMARY]
) on [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

Error Message

Executed as user: Domain1\User2. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:39:01 PM  Error: 2009-07-30 14:39:03.59     Code: 0xC0202009     Source: Controleer nieuwe foutmeldingen Ophalen foutmeldingen [31]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid object name ‘sysssislog’.".  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC020204A     Source: Controleer nieuwe foutmeldingen Ophalen foutmeldingen [31]     Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available.  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC004706B     Source: Controleer nieuwe foutmeldingen SSIS.Pipeline     Description: "component "Ophalen foutmeldingen" (31)" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC004700C     Source: Controleer nieuwe foutmeldingen SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC0024107     Source: Controleer nieuwe foutmeldingen      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:39:01 PM  Finished: 2:39:03 PM  Elapsed:  2.109 seconds.  The package execution failed.  The step failed.

CustomEvenLogger for SSISPackage Script Task

Imports System
Imports System.Diagnostics

‘ This class is used for logging to a customeventlog from a SSISPackage Script Taks
‘ By using a ExceptionCategories enum, we can guide the developers to use structured error messages.

‘ [10-JUL-2008 RVL] Created

‘ [RFC] Get standardMessage from *.resx file.
Public Class Logger
    Dim eventLogName As String = "C221A814-192E.SSISPackages.Package1"
    Dim eventLogSource As String = "C221A814-192E.SSISPackages.Package1"
    Dim eventLogMachineName As String = "."
    Enum ExceptionCategories
        UnExpectedError
    End Enum

    ‘Log exception to EventLog and Console
    Public Sub LogExceptionEvent(ByVal message As String, ByVal ex As Exception, ByVal exceptionCategory As ExceptionCategories)
        If String.IsNullOrEmpty(message) Then
            message = ""
        End If

        If Not EventLog.SourceExists(eventLogSource, eventLogMachineName) Then
            EventLog.CreateEventSource(eventLogSource, eventLogName, eventLogMachineName)
        End If

        Dim standardMessage As String
        Dim eventId As Integer
        Dim eventSubCategory As Short = 1
        Select Case exceptionCategory
            Case ExceptionCategories.UnExpectedError
                standardMessage = "Unknow error occured"
                eventId = 1
            Case Else
                standardMessage = "Unknow Enum ExceptionCategories, developer forgot the implement a case statement for this exception category"
                eventId = 1000
        End Select

        Dim exceptionMessage As String = String.Empty
        Dim exceptionStackTrace As String = String.Empty
        Dim innerExceptionMessage As String = String.Empty
        Dim innerExceptionStackTrace As String = String.Empty

        If Not ex Is Nothing Then
            If Not String.IsNullOrEmpty(ex.Message) Then
                exceptionMessage = ex.Message
            End If
            If Not String.IsNullOrEmpty(ex.StackTrace) Then
                exceptionStackTrace = ex.StackTrace
            End If
            If Not ex.InnerException Is Nothing Then
                If Not String.IsNullOrEmpty(ex.InnerException.Message) Then
                    innerExceptionMessage = ex.InnerException.Message
                End If
                If Not String.IsNullOrEmpty(ex.InnerException.StackTrace) Then
                    innerExceptionStackTrace = ex.InnerException.StackTrace
                End If
            End If
        End If

        Dim el As New EventLog(eventLogName, eventLogMachineName, eventLogSource)
        el.WriteEntry("Standard Message [" & standardMessage & "] " _
        & "ExtendedMessage [" & message + "] " _
        & "] ExceptionMessage [" & exceptionMessage + "] " _
        & "] ExceptionStackTrace [" & exceptionStackTrace + "] " _
        & "] InnerExceptionMessage [" & innerExceptionMessage + "] " _
        & "] InnerExceptionStackTrace [" & innerExceptionStackTrace + "] " _
        , EventLogEntryType.Error, eventId, eventSubCategory)
    End Sub
End Class

System.OutOfMemoryException on SSIS Script Task

I created a SSIS package with a script task in it. The script task created a XML file, but consumed more then 256MB of memory. The default value for the MemToLeave setting.

MemToLeave is virtual address space (VAS) that’s left un-used when SQL Server starts so that external components called by SQL Server are saved some address space. So in order for these technologies, .NET CLR and SSIS, to operate
see http://www.johnsansom.com/index.php/2009/03/sql-server-memory-configuration-determining-memtoleave-settings/

Error

Event Type:    Error
Event Source:    SSIS Package CDS Rapportage
Event Category:    None
Event ID:    3
Date:        4/21/2009
Time:        12:59:29 PM
User:        N/A
Computer:    NLPRC173N59
Description:
Unexpected exception Exception.Message[Exception of type ‘System.OutOfMemoryException’ was thrown.]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

Solution


Setting the MemToLeave to 512MB solved the problem.

SSIS package configuration

We have chosen to use a windows environment variable to hold the path to the *.dtsConfig, which is used to configure the *.dtsx package.

– Close all open Microsoft Visual Studio 2005 instances (because changing windows environment variables will have nog effect)

– Add a Windows Environment Variable:

– start > Control Panel > System > Advanced system settings > Environment Variables > New…

– Variable name: SSISPackage_Configuration

– Variable value: C:\Temp\SSISPackage.dtsConfig

– Click on [OK]

– Click on [OK]

 

– Open the Microsoft Visual Studio 2005 solution containing the SSIS project.

– Open the *.dtsx

– Click in the top menu on SSIS > Package Configurations …

– Check the Enable package configurations

image

– Click on Add …

image

image

image

When you start debugging the SSIS package in Microsoft Visual Studio 2005, the configuration will be red from the C:\Temp\SSISPackage.dtsConfig.

In production you can change the path of the environment variable and the configuration will then be red from that location.