SSIS Script Task:Error: The Script Task is corrupted.

There are probably a lot of SSIS corruption errors, but that is one that is very easy to solve.

The whole error message is texted like this:

Script Task:Error: The Script Task is corrupted.

Script Task:Error: There were errors during task validation.

Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_74aca886806a416fa34ae89cac6237c2" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services. at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

That error came from an execution of SSIS package and it points that particular Script task is corrupted.

It is very confusing, because if you try to run the same package in the Visual Studio everything will be fine:

Just to be short: The error is caused not by corruption during deployment and not by corruption of SSIS DLL library, but just by different version of the compiled code.

Here is how to reproduce that error:
1. Create a package with a Script Task

2. Keep it simple - use C# as a language and click "Edit Script":

3. Enter only one line of a code "int TestVariable = 0;" into the Main section:

4. Then build a solution and deploy it on the SSIS server.
5. On SSIS server execute newly deployed solution:

6. When SSMS asks you want to see a report click "Yes"

7. As the result you will see "Status: Failed". If you click "View Messages" you'll see error details:

Here is how to Fix that problem:
1. In the menu chose Project's properties:

2. In the "Configuration Properties" section you will see TargetServerVersion. In my case it is 2017, while my SQL Server is only at version 2016.

3. The fix is simple: Just specify version of your SQL Server, where you want to deploy that package. Save, re-build/re-compile and re-deploy.

As the result, execution status of your SSIS package has to be "Succeeded":

Hope that post saves you a little time.

Giving special rights for group or user

SQL Server provides great flexibility for different types of security solutions.
In this post I want to show one of them.

Problem description:
1. Need to create a group/user "User1", which has to have only CRUD (Create-Read-Update-Delete) permissions for data in schema called "Schema1".
2. Need to create a group/user "User2", which has to have similar permissions as "User1" and have to be able create Views/Procedures/Functions in schema called "Schema2".
3. The group/user "User1" has to have Select/Execute permissions for all newly created objects in "Schema2".

Solution: Create a special database role for group/user "User2".

Here is how it can be done:

Preparation:

/* Create Test database */
use master;
GO
CREATE DATABASE TestPermissionDB;
GO
ALTER DATABASE [TestPermissionDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT;
GO
USE TestPermissionDB;
GO

/* Create Test Schemas */
CREATE SCHEMA Schema1;
GO 
CREATE SCHEMA Schema2;
GO 
/* Create Test Users and give them CRUD permissions*/
CREATE USER User1 WITH PASSWORD=N'39+pjFkM6+9ll131C9RBWIYL4KcaFSHIqYwK16+B9ec=';
GO
CREATE USER User2 WITH PASSWORD=N'sw/Efa4VCM6bUrz5s+jl8zuRas5r6U8IP8eiUO83NTk=';
GO
ALTER ROLE [db_datareader] ADD MEMBER User1;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User1;
GO
ALTER ROLE [db_datareader] ADD MEMBER User2;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User2;
GO
/* Create Test table */
SELECT 'ABC' as Sample_Data
INTO Schema1.tbl_Sample_Table;
GO

Give special permissions for User1:

/* Grant Permissions for User1 */
GRANT EXECUTE TO User1;
GO

Create special Role:

/* Create special test role and give it specific permissions */
CREATE ROLE SpecialRole;
GO
GRANT CREATE PROCEDURE to SpecialRole;
GO
GRANT CREATE VIEW to SpecialRole;
GO
GRANT CREATE FUNCTION to SpecialRole;
GO

And here is the magic.

/* Associate special test role with Schema2 and assign User2 to that role */
ALTER AUTHORIZATION ON SCHEMA::Schema2 TO SpecialRole;
GO
ALTER ROLE SpecialRole ADD MEMBER User2;
GO

From this point User2 has all permissions it needs.

Test User2 Permissions:

/* test Permissions for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE FUNCTION Schema2.fn_Test() RETURNS CHAR(3) as 
BEGIN
RETURN (SELECT TOP 1 Sample_Data FROM Schema1.tbl_Sample_Table)
END;
GO  
CREATE VIEW Schema2.vw_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE Schema2.sp_Test @VAL CHAR(3) as 
SELECT * FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = @VAL;
GO  
REVERT ;  
GO 

Everything should execute successfully.

Now will test restrictions for User2:

/* test Permission failures for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE VIEW Schema1.vw_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE dbo.sp_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
REVERT ;   

It should return following errors:
Msg 2760, Level 16, State 1, Procedure vw_Failed_Test, Line 1 [Batch Start Line 92]
The specified schema name "Schema1" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure sp_Failed_Test, Line 1 [Batch Start Line 95]
The specified schema name "dbo" either does not exist or you do not have permission to use it.

Test permissions for User1:

 /* test Permissions for User1*/

EXECUTE AS USER = 'User1' ;  
GO  
INSERT INTO Schema1.tbl_Sample_Table VALUES ('XYZ');
GO
EXEC Schema2.sp_Test 'XYZ';
GO
UPDATE Schema1.tbl_Sample_Table 
SET Sample_Data = '123' 
WHERE Sample_Data = 'XYZ';
GO
SELECT * FROM Schema2.vw_Test;
GO
DELETE FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = '123';
GO
SELECT Schema2.fn_Test() as Function_Result;
GO
REVERT ;  
GO

It should return no errors:

If we try to use User1 to create an object like this:

/* test Permission failures for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
CREATE PROCEDURE Schema2.sp_Failed_Test as SELECT 1 as ABC;
GO
REVERT ;  
GO

Will get an error:
Msg 262, Level 14, State 18, Procedure sp_Failed_Test, Line 1 [Batch Start Line 130]
CREATE PROCEDURE permission denied in database 'TestPermissionDB'.

Do not forget to drop test database at the end:

/* Drop Test database */
use master;
GO
DROP DATABASE TestPermissionDB;
GO

Drawing SQL Sphere

<= Drawing 3D Cube

Couple of years ago I came up with an algorithm of drawing an ellipse using SQL Server spatial geometry: http://slavasql.blogspot.com/2015/02/drawing-ellipse-in-ssms.html

I've used that algorithm to make a sphere and as in my previous blog of drawing 3D Cube I use external procedure to simplify the process.
This time instead of temporary stored procedure I'm using a function to generate Geometrical content.
Here is the function's code:

SET NOCOUNT ON
GO
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Elipse') IS NOT NULL
DROP FUNCTION dbo.Elipse
GO
CREATE FUNCTION dbo.Elipse(
@x FLOAT = 0,   -- x coordinate of center
@y FLOAT = 0,   -- y coordinate of center
@rh FLOAT = 1, -- Horizontal radius
@rv FLOAT = 0, -- Vertical Radius
@as FLOAT = 0, -- Starting angle
@af FLOAT = 0 -- Finishing angle
) RETURNS GEOMETRY AS
BEGIN
SELECT @af = CASE @af WHEN 0 THEN 2*Pi() ELSE @af END
 , @rv = CASE @rv WHEN 0 THEN @rh ELSE @rv END

DECLARE @MP VARCHAR(MAX)='';
-- angle that will be increased each loop
DECLARE @theta FLOAT = @as;
-- amount to add to theta each time (degrees)
DECLARE @step FLOAT = 2 * PI()/1000;  
DECLARE @x1 FLOAT, @y1 FLOAT
, @x2 FLOAT = @x + @rh * SIN(@theta)
, @y2 FLOAT = @y + @rv * COS(@theta);

WHILE @theta <= @af
SELECT 
    @x1 = @x2, @y1 = @y2,
    @x2 = @x + @rh * SIN(@theta), 
    @y2 = @y + @rv * COS(@theta), 
    @theta += @step,
 @MP = @MP + '(' 
        + CAST(@x1 as VARCHAR) +  ' ' 
        + CAST(@y1 as VARCHAR) + ',' 
        + CAST(@x2 as VARCHAR) +  ' ' 
        + CAST(@y2 as VARCHAR) + '),';

RETURN (CONVERT(GEOMETRY,'MULTILINESTRING(' 
        + LEFT(@MP,LEN(@MP)-1) + ')')).MakeValid();
END
GO

The easiest way to use that function is to run a SELECT statement of it with some parameters:

SELECT tempdb.dbo.Elipse (10, 10, 1, .5, default, default).STBuffer(0.002);

If you decide to not specify the fourth parameter (@rv - Vertical Radius) you'll get a perfect circle!

SELECT tempdb.dbo.Elipse (0,0,1,default,default,default).STBuffer(0.005);

If you will specify starting and finishing angles you can get a part of an ellipse like this:

SELECT tempdb.dbo.Elipse (10,10,1,0.5,Pi()/4,5*Pi()/4).STBuffer(0.01);

And finally, if you combine several ellipses into one picture you can get a sphere:

/*
Make sphere with Axial tilt 23.437%
*/
DECLARE @d FLOAT = RADIANS(23.437), /*Axial tilt*/
@LR FLOAT = Pi()/4, /* Rotation LEft-Right*/
@x FLOAT = 0, /*Coordinate X*/
@y FLOAT = 0, /*Coordinate Y*/
@r FLOAT = 1, /*Radius*/
@w FLOAT = 0.001  /*Line width*/

DECLARE @g TABLE (g GEOMETRY);

-- Main circle
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (@x, @y, @r, default, default, default )

-- North Pole
INSERT INTO @g SELECT CONVERT(GEOMETRY,'POINT('
       +CAST(@x as VARCHAR)+' '+CAST(@y + @r * COS(@d) as VARCHAR)
       +')').STBuffer(@w)

-- Draw Equator:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (@x, @y, @r, @r * SIN(@d), Pi()/2, 3*Pi()/2)

-- Draw 30 degrees north:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y + @r * COS(Pi()/6)*COS(@d), 
 @r*SIN(Pi()/6), 
 @r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
 ACOS( TAN(@d) / TAN(Pi()/6) )
 , 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/6)  ))

-- Draw 60 degrees north:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y + @r * COS(Pi()/3)*COS(@d), 
 @r*SIN(Pi()/3), 
 @r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
 ACOS( TAN(@d) / TAN(Pi()/3) )
 , 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 60 degrees South:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y - @r * COS(Pi()/3)*COS(@d), 
 @r*SIN(Pi()/3), 
 @r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
 Pi()-ACOS( TAN(@d) / TAN(Pi()/3) )
 , Pi()+ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 30 degrees south:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y - @r * COS(Pi()/6)*COS(@d), 
 @r*SIN(Pi()/6), 
 @r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
 Pi()-ACOS( TAN(@d) / TAN(Pi()/6) )
 , Pi()+ACOS( TAN(@d) / TAN(Pi()/6)  ))

SELECT g.STBuffer(0.005) FROM @g
GO

That sphere is supposed to represent the Earth looking at the Sun at Summer Solstice.