World of Whatever

A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Loading...

Wednesday, December 10, 2014

Biml - Reorganize Index Task

Biml - Task

The Reorganize Index Task via Biml. As always, ADO.NET connection required.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>
    <Packages>
        <Package
            ConstraintMode="Linear"
            Name="Task_ReorganizeIndex">
            <Tasks>
                <ReorganizeIndex
                    ConnectionName="CM_ADO_DB"
                    DatabaseSelectionMode="All"
                    ObjectSelectionMode="Tables"
                    Name="RO All Tables">
                </ReorganizeIndex>

                <!-- Reorg a specific index -->
                <ReorganizeIndex
                    ConnectionName="CM_ADO_DB"
                    DatabaseSelectionMode="Specific"
                    ObjectSelectionMode="Views"
                    Name="RO Specific View"
                    CompactLargeObjects="false">
                    <Databases>
                        <Database>AdventureWorks2012</Database>
                    </Databases>
                    <Objects>
                        <Object>Production.vProductAndDescription</Object>
                    </Objects>
                </ReorganizeIndex>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

The above Biml describes a package that creates two Reorganize Index Tasks. The first reorganizes indexes on all the tables while the second targets a specific indexed view.

Control flow - Reorganize Index Task

RO ALL Tables

Reorganize all the things!

Reorganize all tables

RO Specific View

Here we reorganize a specific table but notice that this dialog makes it appear nothing is selected. Once you open the combobox or click View TSQL, you'll get a different story.

Reorganize specific view

View TSQL

Notice the expected TSQL shows that we're reorganizing Production.vProductAndDescription and Person.vStateProvinceCountryRegion. Weird

Generated TSQL

Thursday, October 30, 2014

Biml and Looping through excel files in SSIS

Biml and Looping through excel files in SSIS

I came across a question on DBA.StackExchange.com and they wanted to know why they were getting an error about the file being locked. My fine compatriots already suggested the questioner use ProcMon but the person was rather addament that it wasn't open. psssst it's open I cover the steps on file in use by another process.

Where's the biml and Excel?

Easy with the pitchforks... The questioner was implementing Mike Davis's Loop Through Excel Files in SSIS and I figureed since I didn't have a Biml post covering Excel yet, I'd pop one out.

Biml and Excel

The only oddity to make note of is the connection string. Normally, your connection string would look something like Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ssisdata\Excel\USCustomers1.xls;Extended Properties="Excel 8.0;HDR=YES"; However, you're going to need to escape those double quotes and your normal tricks of "" or \" aren't going to work here as that's xml we're dealing with. Instead, you'll need to use &quot;

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <ExcelConnection ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ssisdata\Excel\USCustomers1.xls;Extended Properties=&quot;Excel 8.0;HDR=YES&quot;;" Name="CM_Excel"></ExcelConnection>
    </Connections>
    <Packages>
        <Package Name="LoopThroughExcel" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="strExcelFile"></Variable>
            </Variables>
            <Connections>
                <Connection ConnectionName="CM_Excel">
                    <Expressions>
                        <Expression ExternalProperty="ExcelFilePath" >@[User::strExcelFile]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Tasks>
                <ForEachFileLoop 
                    Folder="C:\ssisdata\Excel" 
                    FileSpecification="*.xls" 
                    ConstraintMode="Linear" 
                    Name="FELC Iterate Excel">
                    <VariableMappings>
                        <VariableMapping Name="0" VariableName="User.strExcelFile"/>
                    </VariableMappings>
                    <Tasks>
                        <Dataflow 
                            Name="DFT Do Excel"
                            DelayValidation="true">
                            <Transformations>
                                <ExcelSource 
                                    ConnectionName="CM_Excel" 
                                    Name="XL_SRC">
                                    <ExternalTableInput Table="Sheet1$"></ExternalTableInput>
                                </ExcelSource>
                                <!--
                                Do noting, but do it splendidly
                                -->
                                <DerivedColumns Name="bit bucket"></DerivedColumns>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Success

That should generate a package that has a control flow like

And look at that data flow!

Error

Now, back to the original question. If I open that file in Excel and then run the package, YOU WON'T BELIEVE WHAT ERROR MESSAGE I RECEIVE.</Linkbait>

Error: 0xC0202009 at LoopThroughExcel, Connection manager "CM_Excel": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".

Wednesday, October 22, 2014

A quick and dirty date dimension for PowerPivot

I've built out this sort of thing a few times but in fine fashion, I've never saved my script. In a proper data warehouse, there would be a date dimension built out and I would just reference it. Whenever I get skunkworks projects for things like PowerPivot demos, since that data's not been cared for, I need something handy.

This script generates approximately 20 years of data. It uses an intelligent surrogate key and begins counting at 2014-01-01. It generates date part names and their numeric values for sorting purposes.

SELECT
    -- 20 years, approximate
    TOP (20 * 365)
    CAST(CONVERT(char(8), D.FullDate, 112) AS int) AS DateKey
,   D.FullDate
,   YEAR(D.FullDate) AS YearValue

,   'Q' + DATENAME(QUARTER, D.FullDate) AS QuarterName
,   DATEPART(QUARTER, D.FullDate) AS QuarterValue

,   DATENAME(mm, D.FullDate) AS MonthName
,   MONTH(D.FullDate) AS MonthValue

,   DAY(D.FullDate) AS DayValue

,   DATENAME(dw, D.FullDate) AS DayOfWeekName
,   DATEPART(dw, D.FullDate) AS DayOfWeekValue

FROM
(
    SELECT
        DATEADD(d, D.number, BOT.StartDate) AS FullDate
    FROM
        (
            SELECT
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS number
            FROM
                sys.all_columns AS AC
        ) D
        CROSS APPLY
        (
            -- Start date
            SELECT CAST('2014-01-01' AS date) AS StartDate
        ) BOT
) D;

Thursday, September 25, 2014

Remove all MS_Diagram extended properties

When you create a view in SSMS using the wizard, it retains information for the layout designer. There's no need for this, especially as it makes my database comparisons messy.

I had already run through the following articles the first time to nuke all those metadata items. Then they restored over my dev environment and I had not saved my scripts.

  • http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/25/generate-drop-statements-for-all-extended-properties.aspx
  • http://www.sqlservercentral.com/articles/Metadata/72609/
  • http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/
  • http://msdn.microsoft.com/en-us/library/ms178595.aspx

My approach is a wee different. I'm going to use a cursor to enumerate through my results and then use sp_executesql instead of doing the string building the other fine authors were using.

This script will remove all the MS named objects attached to views. I hope that you can easily adapt this to stripping the extended properties from any object by adjusting the join to other system objects and/or using level2 specifications

DECLARE 
    @Query nvarchar(4000) = 'EXECUTE sys.sp_dropextendedproperty @name, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name;'
,   @ParamList nvarchar(4000) = N'@name sysname, @level0type varchar(128), @level0name sysname, @level1type varchar(128), @level1name sysname, @level2type varchar(128), @level2name sysname'
,   @SchemaName sysname
,   @ObjectName sysname
,   @PropertyName sysname
,   @ObjectType varchar(128);

DECLARE CSR CURSOR
READ_ONLY
FOR 
SELECT
    S.name AS SchemaName
,   V.name AS ObjectName
,   EP.name AS PropertyName
,   O.type_desc AS ObjectType
FROM 
    sys.extended_properties AS EP
    INNER JOIN 
        sys.views V
        ON V.object_id = EP.major_id 
    INNER JOIN 
        sys.schemas S
        ON S.schema_id = V.schema_id 
    INNER JOIN
        sys.objects AS O
        ON O.object_id = V.object_id
WHERE 
    EP.minor_id = 0
    -- The underscore is a single character wild card, need to escape it
    AND EP.name LIKE 'MS_Dia%';


OPEN CSR;

FETCH NEXT 
FROM CSR INTO
    @SchemaName 
,   @ObjectName 
,   @PropertyName 
,   @ObjectType;
WHILE (@@fetch_status = 0)
BEGIN

    EXECUTE sys.sp_executesql 
        @Query
    ,   @ParamList
    ,   @name=@PropertyName
    ,   @level0Type = 'SCHEMA'
    ,   @level0Name = @SchemaName
    ,   @level1Type = @ObjectType
    ,   @level1Name = @ObjectName
    ,   @level2type = NULL
    ,   @level2Name = NULL;


    FETCH NEXT 
    FROM CSR INTO
        @SchemaName 
    ,   @ObjectName 
    ,   @PropertyName 
    ,   @ObjectType;
END

CLOSE CSR;
DEALLOCATE CSR;

Tuesday, September 23, 2014

Behind the scenes with Integration Services Catalogs Create Catalog

What happens you create the SSISDB catalog for the first time? I had no idea and this question had me wondering why in the world they were doing a restore. There's only one way to find out.

To profiler, we go! I ran a TSQL Trace against a 2014 instance (that had previously had an SSISDB catalog so this might be missing some conditional checks). Line breaks added for readability

First step, it creates a table variable and populates it with values from the SSISDB catalog, if the catalog exists.

exec sp_executesql N'
        --Preparing to access the Catalog object
        DECLARE @t_catalogs TABLE (
        Name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
        EncryptionAlgorithm nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        SchemaVersion int,
        SchemaBuild nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        OperationLogRetentionTime int,
        MaxProjectVersions int,
        OperationCleanupEnabled bit,
        VersionCleanupEnabled bit,
        ServerLoggingLevel int,
        OperationLogNumberOfRecords int,
        VersionLogNumberOfRecords int)

        IF DB_ID(''SSISDB'') IS NOT NULL
        BEGIN
        INSERT INTO @t_catalogs VALUES(
        ''SSISDB'',
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''ENCRYPTION_ALGORITHM''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SCHEMA_VERSION''),
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SCHEMA_BUILD''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''RETENTION_WINDOW''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''MAX_PROJECT_VERSIONS''),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''OPERATION_CLEANUP_ENABLED''),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''VERSION_CLEANUP_ENABLED''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SERVER_LOGGING_LEVEL''),
        (SELECT COUNT(operation_id) FROM [SSISDB].[catalog].[operations]),
        (SELECT COUNT(object_id) FROM [SSISDB].[catalog].[object_versions])
        )
        END



SELECT
''IntegrationServices[@Name=''
+ quotename(CAST(SERVERPROPERTY(N''Servername'') AS sysname),'''''''')
+ '']''
+ ''/Catalog[@Name=''
+ ''''''''
+ REPLACE((SELECT Name from @t_catalogs), '''''''', '''''''''''') + '''''''' + '']'' AS [Urn],
(SELECT Name from @t_catalogs) AS [Name],
(SELECT EncryptionAlgorithm from @t_catalogs) AS [EncryptionAlgorithm],
(SELECT SchemaVersion from @t_catalogs) AS [SchemaVersion],
(SELECT SchemaBuild from @t_catalogs) AS [SchemaBuild],
(SELECT OperationLogRetentionTime from @t_catalogs) AS [OperationLogRetentionTime],
(SELECT MaxProjectVersions from @t_catalogs) AS [MaxProjectVersions],
(SELECT OperationCleanupEnabled from @t_catalogs) AS [OperationCleanupEnabled],
(SELECT VersionCleanupEnabled from @t_catalogs) AS [VersionCleanupEnabled],
(SELECT ServerLoggingLevel from @t_catalogs) AS [ServerLoggingLevel],
(SELECT OperationLogNumberOfRecords from @t_catalogs) AS [OperationLogNumberOfRecords],
(SELECT VersionLogNumberOfRecords from @t_catalogs) AS [VersionLogNumberOfRecords]
WHERE
(CAST(SERVERPROPERTY(N''Servername'') AS sysname)=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'RHUDAUR\DEV2014'
It's an interesting query result
UrnNameEncryptionAlgorithmSchemaVersionSchemaBuildOperationLogRetentionTimeMaxProjectVersionsOperationCleanupEnabledVersionCleanupEnabledServerLoggingLevelOperationLogNumberOfRecordsVersionLogNumberOfRecords
IntegrationServices[@Name='RHUDAUR\DEV2014']/Catalog[@Name='SSISDB']SSISDBAES_256312.0.2000.83651011100

Next up, a quick check to make sure I'm in the admin role

SELECT ISNULL(IS_SRVROLEMEMBER ('sysadmin'), 0)

Does the database already exist? The parameterization of this plus the table variable used in the first looks like the code is designed for more than one "SSISDB" catalog. Why you'd want such a thing is an entirely seperate question.

exec sp_executesql N'SELECT name FROM msdb.sys.sysdatabases WHERE name = @dbname',N'@dbname nvarchar(6)',@dbname=N'SSISDB'

Random check for our version. I assume whatever is issuing the commands uses this.

select SUBSTRING (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),1,2);

Hit the registry to see where we have installed the SSIS (DTS) components.

declare @key_value nvarchar(1024);
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
,'SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\Setup\DTSPath'
, N''
, @key_value output;
select @key_value;

Check to verify the .bak file exists. I presume this is built off the preceding query. Returns a 1 if it was found.

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

Now that we know where a backup is and that we're going to create a database called SSISDB, the code verifies the database doesn't already exist. If it does, it terminates the operation with an error The database, 'SSISDB', already exists. Rename or remove the existing database, and then run SQL Server Setup again.

IF DB_ID('SSISDB') IS NOT NULL
    RAISERROR(27135, 16, 1, 'SSISDB') 

Now we recheck the version except this time we force the failure of the script. I like seeing a reference to "Denali" in the error message.

IF CAST( SUBSTRING (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),1,2) AS INT ) < 11
    RAISERROR (27193,16,1, 'Denali or later') WITH NOWAIT

As part of the installation of the SSISDB, we must have CLR enabled. I already had mine enabled so I expect there's a step after this that I didn't capture.

SELECT [value_in_use] FROM sys.configurations WHERE [name] = 'clr enabled'

This step builds out the path where the data files should be located

DECLARE @path nvarchar(1024) = Convert(nvarchar(1024),ServerProperty('MasterFile'));
SELECT @path = SUBSTRING(@path, 1, CHARINDEX(N'master.mdf', LOWER(@path)) - 1);
SELECT @path;

We check to see if the SSISDB .mdf file exists

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.mdf'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

And we check to see if the SSISDB .ldf file exists

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.ldf'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

This step generates the files contained within our bak. I assume this is used to generate the next command.

exec sp_executesql N'RESTORE FILELISTONLY FROM DISK = @backupfile'
,N'@backupfile nvarchar(67)'
,@backupfile=N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'

Now we're cooking with gas. Here we actually perform the restore of the SSISDB backup.

exec sp_executesql N'RESTORE DATABASE @databaseName
FROM DISK = @backupFile  WITH REPLACE
,MOVE @dataName TO @dataFilePath
,MOVE @logName TO @logFilePath'
,N'@databaseName nvarchar(6),@dataName nvarchar(4),@dataFilePath nvarchar(75),@logName nvarchar(3),@logFilePath nvarchar(75),@backupFile nvarchar(67)'
,@databaseName=N'SSISDB'
,@dataName=N'data'
,@dataFilePath=N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.mdf'
,@logName=N'log'
,@logFilePath=N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.ldf'
,@backupFile=N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'

If for some reason the restore left the SSISDB in read-only mode, force it into read-write.

USE master;
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name]='SSISDB' AND [is_read_only] = 1)
ALTER DATABASE [SSISDB]
SET READ_WRITE WITH
ROLLBACK IMMEDIATE

At this point, we have an SSISDB but it's not secure. We have the ability to store sensitive data in there so we need to protect our jewels.

USE [SSISDB];

IF EXISTS (SELECT [name] FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##')
    DROP MASTER KEY

Secure our database with a master key

exec sp_executesql N'USE [SSISDB];
DECLARE @pwd nvarchar(4000) = REPLACE(@password, N'''''''', N'''''''''''');
EXEC(''CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''''' + @pwd + '''''''');'
,N'@password nvarchar(20)',@password=N'pass@word1'

Create an asymmetric key from our assembly

IF NOT EXISTS(SELECT * FROM sys.asymmetric_keys WHERE name = 'MS_SQLEnableSystemAssemblyLoadingKey')
CREATE ASYMMETRIC KEY
    MS_SQLEnableSystemAssemblyLoadingKey 
FROM
    EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll' 

I have no idea what this virtual account is for and why we'll drop it if it exists but so be it. I assume we're doing this to ensure it has the correct permissions in the next step.

IF EXISTS(SELECT [name] FROM sys.server_principals where name = '##MS_SQLEnableSystemAssemblyLoadingUser##')
DROP LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser##

Create a login that can create unsafe assemblies. An unsafe assembly? Cats and dogs are signing leases at this very moment.

CREATE LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser## FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
GRANT UNSAFE ASSEMBLY TO ##MS_SQLEnableSystemAssemblyLoadingUser##

I allowed the SSIS CLR to run on startup so the installing is obliging me. Here it drops it in case it existed.

IF EXISTS(SELECT name FROM sys.procedures WHERE name=N'sp_ssis_startup')
BEGIN
   EXEC sp_procoption N'sp_ssis_startup','startup','off'
   DROP PROCEDURE [sp_ssis_startup]
END

Creation of the stored procedure dbo.sp_ssis_startup in master.

CREATE PROCEDURE [dbo].[sp_ssis_startup]
AS
SET NOCOUNT ON
 /* Currently, the IS Store name is 'SSISDB' */
 IF DB_ID('SSISDB') IS NULL
  RETURN

 IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N'startup')
  RETURN

 /*Invoke the procedure in SSISDB  */
 EXEC [SSISDB].[catalog].[startup]

I have no idea why they have IF here but they do.

IF (1=1)
BEGIN
 /* Run sp_ssis_startup when Sql Server restarts */
 EXEC sp_procoption N'sp_ssis_startup','startup','on'
END

At this point, we are going to get busy with setting up maintenance for the SSISDB. Drop any jobs named "SSIS Server Maintenance Job" and hope that you didn't have anything vitally important with the same name.

IF EXISTS (SELECT name FROM sysjobs WHERE name = N'SSIS Server Maintenance Job')
EXEC sp_delete_job
    @job_name = N'SSIS Server Maintenance Job' ;

Drop an existing virtual login that will be associated to our job.

IF EXISTS(SELECT * FROM sys.server_principals where name = '##MS_SSISServerCleanupJobLogin##')
    DROP LOGIN ##MS_SSISServerCleanupJobLogin##

Create our login

DECLARE @loginPassword nvarchar(256)
SELECT @loginPassword = REPLACE (CONVERT( nvarchar(256), CRYPT_GEN_RANDOM( 64 )), N'''', N'''''')
EXEC ('CREATE LOGIN ##MS_SSISServerCleanupJobLogin## WITH PASSWORD =''' +@loginPassword + ''', CHECK_POLICY = OFF')

Disable the login we just created...

ALTER LOGIN ##MS_SSISServerCleanupJobLogin## DISABLE

Create our job, owned by the disabled login above

EXEC dbo.sp_add_job
    @job_name = N'SSIS Server Maintenance Job',
    @enabled = 1,
    @owner_login_name = '##MS_SSISServerCleanupJobLogin##',
    @description = N'Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project.'

Cleanup, aisle 1. The job step runs a stored procedure to do the cascading deletes


DECLARE @IS_server_name NVARCHAR(30)
SELECT @IS_server_name = CONVERT(NVARCHAR, SERVERPROPERTY('ServerName'))

EXEC sp_add_jobserver  @job_name = N'SSIS Server Maintenance Job',
                       @server_name = @IS_server_name

EXEC sp_add_jobstep
    @job_name = N'SSIS Server Maintenance Job',
    @step_name = N'SSIS Server Operation Records Maintenance',
    @subsystem = N'TSQL',
    @command = N'EXEC [internal].[cleanup_server_retention_window]',
    @database_name = N'SSISDB',
    @on_success_action = 3,
    @retry_attempts = 3,
    @retry_interval = 3;

Clean up the old versions of the .ispac files

EXEC sp_add_jobstep
    @job_name = N'SSIS Server Maintenance Job',
    @step_name = N'SSIS Server Max Version Per Project Maintenance',
    @subsystem = N'TSQL',
    @command = N'EXEC [internal].[cleanup_server_project_version]',
    @database_name = N'SSISDB',
    @retry_attempts = 3,
    @retry_interval = 3;

Create a schedule to run daily at midnight. Again, this might not be optimal for your ETL processing window (see link above).

EXEC sp_add_jobschedule
    @job_name = N'SSIS Server Maintenance Job',
    @name = 'SSISDB Scheduler',
    @enabled = 1,
    @freq_type = 4, /*daily*/
    @freq_interval = 1,/*every day*/
    @freq_subday_type = 0x1,
    @active_start_date = 20001231,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 120000

Finally, we need to ensure our user can run the two stored procedure in the job (makes sense). Drop that user, like it's hot...

USE SSISDB
IF EXISTS (SELECT name FROM sys.database_principals WHERE name = '##MS_SSISServerCleanupJobUser##')
    DROP USER ##MS_SSISServerCleanupJobUser##

Add the user back in, based on our disabled login.

CREATE USER ##MS_SSISServerCleanupJobUser## FOR LOGIN ##MS_SSISServerCleanupJobLogin##

Give them rights to run internal.cleanup_server_retention_window

GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO ##MS_SSISServerCleanupJobUser##

Give them rights to run internal.cleanup_server_project_version

GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO ##MS_SSISServerCleanupJobUser##

References

Sunday, September 21, 2014

Biml The connection string format for Connection is not valid

A brief post on resolving errors with Connections in Biml. A friend had sent me some sample Biml and the latest batch wasn't validating. It looked valid, ish, but I was getting
Description: The connection string format for Connection X is not valid Recommendation: Please supply one or more components of the form X=Y, separated by semicolons The supplied code was
<Connection 
    Name="CM_OLE" 
    ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;" 
/>
So, what's the issue? It's the trailing space on the ConnectionString. It seems my friends text editor/email program decided to "help" his code by adding that space.

Resolution

Fix the connection string by removing the trailing space inside the ConnectionString property.

Monday, August 4, 2014

Dead SQL Objects Code Query

Bring out your dead!

Just a quick post to share the script. At my current client, they have a large codebase that's not been cared for. I give them credit for generally avoiding dynamic SQL but the proliferation of autogenerated code has resulted in thousands, not an exaggeration, of objects that were generated and never used. I had run down fixing a dependency chain only to learn the rootmost object was broken as the day is long.

There has to be a better way of figuring this out. SQL Server Data Tools you say? Yeah, it took 2 days to reverse engineer the database and validate the dependencies only to run out of memory. I settled on sys.sp_refreshsqlmodule to at least give me a fighting chance of finding out what's broken in the database. The following query generates a list of all non-schemabound objects and then calls sys.sp_refreshmodules against the objects. if it raises an error, I drop it into a table so I can inspect it later as well as print out an error message. Quick and dirty, there's probably some logic issues in there, things that aren't dead yet, but this at least helps me triage the database.

-- This script attempts to generate a report on whether all the SQL modules are valid
-- in a database. This can generate false positives as there is no intelligence
-- built into the order to ensure object dependencies are followed.
SET NOCOUNT ON;
DECLARE 
    @SchemaName sysname
,   @ObjectName sysname
,   @QualifiedObjectName sysname
,   @QueryTemplate nvarchar(1000) = N'EXECUTE sys.sp_refreshsqlmodule @name;'
,   @ParamList nvarchar(20) = N'@Name sysname';


DECLARE ObjectCursor CURSOR READ_ONLY
FOR 
SELECT ALL
    SCHEMA_NAME(S.schema_id) AS SchemaName
,   S.name AS ObjectName
FROM 
    sys.objects AS S
    INNER JOIN
    (
        -- schema bound objects can't get recompiled
        SELECT
            SM.object_id
        FROM
            sys.sql_modules AS SM
        WHERE
            SM.is_schema_bound = 0
    ) AS SM
        ON SM.object_id = S.object_id
WHERE 
    S.is_ms_shipped = 0
    -- adjust this list as needed
    AND S.type in ('FN', 'IF', 'IT', 'P', 'TF', 'TR', 'V')
ORDER BY S.schema_id
;

DECLARE @ManualCleanup TABLE
(
    SchemaName sysname
,   ObjectName sysname
,   SQLStatement nvarchar(1000) NOT NULL
);

OPEN ObjectCursor

FETCH NEXT 
FROM ObjectCursor 
INTO @SchemaName
,   @ObjectName;

WHILE (@@fetch_status > -1)
BEGIN
    SET @QualifiedObjectName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@ObjectName);
    BEGIN TRY
        EXECUTE dbo.sp_executesql @queryTemplate, @ParamList, @Name = @QualifiedObjectName;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        INSERT INTO
            @ManualCleanup
        (
            SchemaName
        ,   ObjectName
        ,   SQLStatement
        )
        SELECT
            @SchemaName
        ,   @ObjectName
        ,   'EXECUTE sys.sp_refreshsqlmodule @name = N''' + @QualifiedObjectName + ''';';
    END CATCH

    FETCH NEXT 
    FROM ObjectCursor 
    INTO @SchemaName
    ,   @ObjectName;
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor


SELECT 
    MC.* 
FROM 
    @ManualCleanup AS MC;