DECLARE @instance_name varchar(50) = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'olap_source_db_name')--'AmbicaBI'
DECLARE @ssis_project_name varchar(50) = 'P4S_CREATE_CUBE';
DECLARE @environment_name varchar(70) = @ssis_project_name+'_'+@instance_name;
DECLARE @environment_id int;
DECLARE @server_address varchar(50) = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'olap_server_address')--'dione\sql2012'
DECLARE @relation_server varchar(50) = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'server_address')--'dione\sql2012'
DECLARE @conn_string sql_variant = N'Data Source='+@server_address+';Persist Security Info=False;User ID=ambicaBI_owner;Password=ambicaBIo;Initial Catalog='+@instance_name+';'
DECLARE @inti_catalog sql_variant = N''+@instance_name+''
--DECLARE @job_name varchar(50) = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'olap_job_name')--'P4S_CREATE_CUBE'
DECLARE @job_name varchar(50) = @environment_name
DECLARE @analysis_user varchar(50) = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'analysis_user')--'AMBICA\DsvUser'
DECLARE @query nvarchar(MAX) = ''
DECLARE @analysis_user_pw varchar(50) = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'analysis_user_pw')--'AMBICA\DsvUser'
DECLARE @reference_id int



IF NOT EXISTS (SELECT TOP 1 1 FROM [SSISDB].[catalog].[folders] WHERE name = 'Plan4s')
    EXEC [SSISDB].[catalog].[create_folder] @folder_name=N'Plan4s'

IF (SELECT TOP 1 1 FROM [SSISDB].[catalog].[environments] WHERE name = @environment_name) = 1
    EXEC [SSISDB].[catalog].[delete_environment] @folder_name = 'Plan4s', @environment_name = @environment_name
EXEC [SSISDB].[catalog].[create_environment] @environment_name = @environment_name, @environment_description=N'Environment for AmbicaBI OLAP module', @folder_name=N'Plan4s'

IF (SELECT TOP 1 1 FROM [SSISDB].[catalog].[environment_references] WHERE environment_name = @environment_name) = 1 BEGIN
    SET @reference_id = (SELECT TOP 1 reference_id FROM [SSISDB].[catalog].[environment_references] WHERE environment_name = @environment_name)
    EXEC [SSISDB].[catalog].[delete_environment_reference] @reference_id = @reference_id
END
EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name=@environment_name, @reference_id=@environment_id OUTPUT, @project_name=N'P4S_CREATE_CUBE', @folder_name=N'Plan4s', @reference_type=R


EXEC [SSISDB].[catalog].[create_environment_variable]
    @variable_name= 'ConnectionString',
    @sensitive=False,
    @description= 'Connection String',
    @environment_name = @environment_name,
    @folder_name= 'Plan4s',
    @value= @conn_string,
    @data_type= 'String'

EXEC [SSISDB].[catalog].[create_environment_variable]
    @variable_name= 'InitialCatalog',
    @sensitive=False,
    @description= 'Initial Catalog',
    @environment_name = @environment_name,
    @folder_name= 'Plan4s',
    @value= @inti_catalog,
    @data_type= 'String'

EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name=N'InitialCatalog', @object_name=N'PackageV5.dtsx', @folder_name=N'Plan4s', @project_name=N'P4S_CREATE_CUBE', @value_type=R, @parameter_value=N'InitialCatalog'
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name=N'ConnectionString', @object_name=N'PackageV5.dtsx', @folder_name=N'Plan4s', @project_name=N'P4S_CREATE_CUBE', @value_type=R, @parameter_value=N'ConnectionString'


SET @query = '
USE [SSISDB]
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.database_principals WHERE name = ''' + @analysis_user + ''') BEGIN
    CREATE USER [' + @analysis_user + '] WITH DEFAULT_SCHEMA=dbo;
END

USE [SSISDB]
ALTER USER [' + @analysis_user + '] WITH DEFAULT_SCHEMA=dbo
USE [SSISDB]
ALTER ROLE [ssis_admin] ADD MEMBER [' + @analysis_user + ']


USE [msdb]
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.database_principals WHERE name = ''' + @analysis_user + ''') BEGIN
    CREATE USER [' + @analysis_user + '] WITH DEFAULT_SCHEMA=dbo;
END

USE [msdb]
ALTER USER [' + @analysis_user + '] WITH DEFAULT_SCHEMA=dbo
USE [msdb]
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [' + @analysis_user + ']'

--print @query
EXEC (@query)

--GO

set ansi_padding ON

DECLARE @jobId binary(16)
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @job_name)
IF (@jobId IS NOT NULL)
BEGIN
    EXEC msdb.dbo.sp_delete_job @jobId
END

SET @query = '
IF NOT EXISTS (SELECT * FROM msdb.sys.server_principals WHERE name = ''' + @analysis_user + ''') BEGIN
    CREATE LOGIN [' + @analysis_user + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
END

IF NOT EXISTS (SELECT * FROM msdb.sys.credentials WHERE name = ''Plan4sJobRun'') BEGIN
    CREATE CREDENTIAL Plan4sJobRun WITH IDENTITY = ''' + @analysis_user + ''', SECRET= ''' + @analysis_user_pw + '''
END'
EXEC(@query)

--DELETE FROM amb.Plan4SParameters WHERE ParameterName = 'analysis_user_pw'
UPDATE amb.Plan4SParameters SET ParameterValue = '' WHERE ParameterName = 'analysis_user_pw'

IF NOT EXISTS (SELECT TOP 1 1 FROM msdb..sysproxies WHERE name = 'Plan4sJobRun') BEGIN
    EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Plan4sJobRun', @credential_name=N'Plan4sJobRun', @enabled=1
    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Plan4sJobRun', @subsystem_id=11
    EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'Plan4sJobRun', @login_name=N'ambicaBI_owner'
END


SET @query =
'BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''[Uncategorized (Local)]'' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''[Uncategorized (Local)]''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N''' + @job_name + ''',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N''No description available.'',
        @category_name=N''[Uncategorized (Local)]'',
        @owner_login_name=N''ambicaBI_owner'', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Step [Run SSIS package] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Run SSIS package'',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=4,
        @on_success_step_id=2,
        @on_fail_action=4,
        @on_fail_step_id=3,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N''SSIS'',
        @command=N''/ISSERVER "\"\SSISDB\Plan4s\' + @ssis_project_name + '\PackageV5.dtsx\"" /SERVER "\"' + @relation_server + '\"" /ENVREFERENCE ' + CAST(@environment_id AS VARCHAR) + ' /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'',
        @database_name=N'''+@instance_name+''',
        @flags=0,
        @proxy_name=N''Plan4sJobRun''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Step [Mark SUCCESS] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Mark SUCCESS'',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N''TSQL'',
        @command=N''UPDATE olap.CreateCubeInput
SET Done = 1, EndDateTime = GETDATE() , Result = ''''Success''''
WHERE Done = 0'',
        @database_name=N'''+@instance_name+''',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Step [Mark ERROR] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Mark ERROR'',
        @step_id=3,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N''TSQL'',
        @command=N''UPDATE olap.CreateCubeInput
SET Done = 1, EndDateTime = GETDATE() , Result = ''''Error'''',SsisLogEntry = (select top 1 message
from dbo.sysssislog
where (event=''''User:ScriptTaskLogEntry'''' or event =''''OnError'''')
and source=''''Build and process cube''''
and message not like ''''%modal dialog box%''''
order by starttime desc)
WHERE Done = 0'',
        @database_name=N'''+@instance_name+''',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

'
EXEC (@query)
--print @query