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.

Check if a table exists in a SQL Server 2008 database

To check if a table exists in a SQL Server 2008 database, use the following T-SQL code:
Use the system view sys.objects and not the old sql server 2000 system table sysobjects


if
not exists (select 1 from sys.objects o where o.name =’TableName’ and o.type =’U’)
begin
    create table
dbo.TableName (
      
Id int identity(1,1) not nullprimary key,
      
Barcode varchar(35) not null

     )
end
go