Management Studio – Faster with multiple select

by Arnaud Gastelblum | 30 Sep 2019 | SQL Server, Tips & Tricks

In SQL Server Management Studio (SSMS for close friends) but also in a multitude of text editors (such as Notepad++ for example), you can make multiple selections.

I thought it was something everyone knew, but I realize that I often look like a magician every time I do it.

I’m delighted to pretend to be Harry Potter, but I think it’s time for this little game to stop!

Look by yourself how simple it is!

How?

You should press the keys [ALT] + [Shift] simultaneously and move your cursor [Up] and / or [Down] to select your text.

One useful case

Sometimes we have to surround our text with single quotes. (for example: to add multiple codes to an IN clause in a test query)

No need to add them one by one and make sure you do not have space at the end.
Here is the method:

Like mentioned above, this tip is not an exclusivity in SSMS, you can also do the same in many different text editor.

SSIS – Create Environment from Packages variables

by Arnaud Gastelblum | 2 Aug 2019 | SSIS, Tips & Tricks

I created this script to automate some “not funny” tasks with our SSIS Catalog. If you have several SSIS projects configured in project mode (with a project.params file), when deploy them on your servers you unfortunately have to manually create the different environments.

After deploying SSIS packages, you can run the following query and use the generated SQL code.

the generated SQL script:

Create the different environment (Based on the name of the projects)
Create variables with default values (value available in packages)
Assigning Environments to Projects
Assign the environment variables to the project variables.

The following code is not clean, but it does the work! 🙂

USE SSISDB;

SET NOCOUNT ON;




-------------------               
-- Configuration
-------------------
DECLARE @FolderToExport VARCHAR(50) = 'BI' 

       
DECLARE 
    @folder_id          INT    
   ,@name               VARCHAR(150)
   ,@project_id         INT
   ,@sql                VARCHAR(MAX) = ''
   ,@sqlTemp            VARCHAR(MAX) = '' 
   ,@cr                 CHAR(1) = CHAR(10)
   ,@tab                CHAR(4) = SPACE(4)
   ,@ProjectName        VARCHAR(50)
   ,@VariableName       VARCHAR(150)
   ,@Value              VARCHAR(600)


SET @sql = '';

SET @sql += 'DECLARE @ReturnCode INT=0, @folder_id bigint' + @cr + @cr;       
SET @sql += '-- ---------------------------------------------' + @cr;
SET @sql += '--'+ @tab + 'Variable declarations, make any changes here' + @cr;
SET @sql += '-- ---------------------------------------------' + @cr;
SET @sql += 'DECLARE '+ @cr;
SET @sql += @tab + @tab + ' @folder  sysname' + @cr;
SET @sql += @tab + @tab + ',@env     sysname' + @cr;
SET @sql += @tab + @tab + ',@project sysname;' + @cr;


    


-- Get Folder ID      
SELECT @folder_id = folder_id FROM [internal].[folders] WHERE name = @FolderToExport

PRINT @SQL;
SET @SQL = '';








---------------------------------------------
-- Export Current package Configuration
---------------------------------------------
DECLARE db_cursor_variable CURSOR FOR 
  SELECT DISTINCT
     CONVERT(NVARCHAR(50),  projects.name                    )      AS ProjectName
    ,CONVERT(NVARCHAR(150), projects.name  +'__'+ parameter_name)   AS VariableName
    ,CONVERT(NVARCHAR(600), ISNULL(design_default_value, '')             )      AS Value
    FROM        internal.object_parameters main
    INNER JOIN  internal.projects                 ON main.project_id = projects.project_id
WHERE project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] s WHERE s.project_id = main.project_id)
  AND parameter_name NOT LIKE 'CM.%'
  AND projects.folder_id = @folder_id
   ORDER BY  CONVERT(NVARCHAR(50),  projects.name                    )   
            ,CONVERT(NVARCHAR(150), projects.name  +'__'+ parameter_name)
            ,CONVERT(NVARCHAR(600), ISNULL(design_default_value, ''))   

OPEN db_cursor_variable  
FETCH NEXT FROM db_cursor_variable 
INTO 
      @ProjectName
     ,@VariableName
     ,@Value
WHILE @@FETCH_STATUS = 0  
BEGIN 

    PRINT 'DECLARE @'+ @VariableName +' NVARCHAR(600) = N'''+ @Value +''';';
 
    FETCH NEXT FROM db_cursor_variable 
    INTO 
          @ProjectName
         ,@VariableName
         ,@Value 
END 

CLOSE db_cursor_variable  
DEALLOCATE db_cursor_variable 












SET @SQL += 'SET @folder = '''+ @FolderToExport +''''+ @cr + @cr+ @cr;



-- Begin transaction
SET @sql += ';' + @cr + '/* Starting the transaction */' + @cr;
SET @sql += 'BEGIN TRANSACTION' + @cr + @cr + @cr;                  

----
---- Test if folder exist -> If not create it
--SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder)' + @cr;
--SET @sql += @tab + 'BEGIN' + @cr;
--SET @sql += @tab + @tab + 'RAISERROR(''Creating folder: %s ...'', 10, 1, @folder) WITH NOWAIT;' + @cr;
--SET @sql += @tab + @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_folder] @folder_name=@folder, @folder_id=@folder_id OUTPUT' + @cr;
--SET @sql += @tab + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr;
--SET @sql += @tab + 'END' + @cr + @cr;   


                        


---------------------------------------------
-- Create environment + variables
-----------------------------------------------
DECLARE db_cursor CURSOR FOR 
  SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id
 
          
OPEN db_cursor  
FETCH NEXT FROM db_cursor 
INTO @project_id, @name  

WHILE @@FETCH_STATUS = 0  
BEGIN 

    
    SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;

    SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE name = @env)' + @cr;
    SET @sql += @tab + 'BEGIN' + @cr;

    SET @sql += @tab + @tab +'RAISERROR(''Creating Environment: %s'', 10, 1, @env) WITH NOWAIT;' + @cr;
    SET @sql += @tab + @tab +'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env'  + @cr;
    SET @sql += @tab + @tab +'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr + @cr;
    
    SET @sql += @tab + 'END' + @cr;  

        print @sql;
        SET @sql = '';               

        -- ---------------------------------------------
        -- Generate the variable creation
        -- ---------------------------------------------
        SET @sql += '  -- ProjectId => '+ CONVERT(NVARCHAR(50), @project_id) + @cr;
        SELECT DISTINCT [cmd] = @tab + 'RAISERROR(''   Creating variable: ' + parameter_name + ' ...'', 10, 1) WITH NOWAIT;'           + @cr
                                        + @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]' + @cr
                                        + @tab + @tab + '  @variable_name=N'''    + parameter_name + ''''                     + @cr
                                        + @tab + @tab + ', @sensitive='           + CONVERT(varchar(2), sensitive)  + @cr
                                        + @tab + @tab + ', @description=N'''      + [description] + ''''            + @cr
                                        + @tab + @tab + ', @environment_name=@env'                                     + @cr
                                        + @tab + @tab + ', @folder_name=@folder'                                       + @cr
                                        + @tab + @tab + ', @value=@'            + @name +'__'+ parameter_name        + @cr
                                        + @tab + @tab + ', @data_type=N'''        + parameter_data_type + ''''                     + @cr
                                        + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;'        + @cr
                                , [name] = parameter_name
        INTO #cmd
          FROM [SSISDB].[internal].[object_parameters]
         WHERE project_id = @project_id 
           AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)
           AND parameter_name NOT LIKE 'CM.%'

        WHILE EXISTS (SELECT TOP 1 1 FROM #cmd)
        BEGIN
                SELECT TOP 1 @sql = cmd, @name = name FROM #cmd ORDER BY name;
                PRINT @sql;
                                
                DELETE FROM #cmd WHERE name = @name;
        END;

        DROP TABLE #cmd;
        SET @sql = '';



 
    FETCH NEXT FROM db_cursor INTO @project_id, @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 









 
---------------------------------------------
-- Set Environment on each Project
--       + Set Environment variables on each Project Variables
---------------------------------------------
DECLARE db_cursor CURSOR FOR 
  SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id
 
          
OPEN db_cursor  
FETCH NEXT FROM db_cursor 
INTO @project_id, @name  


SET @sql = 'DECLARE @reference_id AS BIGINT'+ @cr;
PRINT @sql;

WHILE @@FETCH_STATUS = 0  
BEGIN          
        SET @sql =  @tab + 'SET @project = '''+ @name +''';'+ @cr;   
        SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;
        SET @sql += 'RAISERROR(''Configure Project: %s ...'', 10, 1, @project) WITH NOWAIT;' + @cr + @cr

        SET @sql += '-- ------------------------------------------    '+ @cr
        SET @sql += '-- Set Environment to Project  '                  + @cr
        SET @sql += '-- ------------------------------------------    '+ @cr
        SET @sql += 'RAISERROR(''   Set Environment (%s) as Reference ...'', 10, 1, @env) WITH NOWAIT;' + @cr
        SET @sql += 'EXEC SSISDB.catalog.create_environment_reference'+ @cr
        SET @sql += @tab + @tab + '@folder_name      = @folder,     -- Folder (SSIDB >> "FolderName")'+ @cr
        SET @sql += @tab + @tab + '@environment_name = @env,     -- Environment Name              '+ @cr
        SET @sql += @tab + @tab + '@project_name     = @project,     -- Project Name                  '+ @cr
        SET @sql += @tab + @tab + '@reference_type   = ''R'','+ @cr
        SET @sql += @tab + @tab + '@reference_id     = @reference_id OUTPUT'+ @cr

        PRINT @SQL
                                         


        -- ------------------------------------------
        -- Set Environment Variable to Project Variables
        -- ------------------------------------------


        IF OBJECT_ID('tempdb..#cmd2')     IS NOT NULL DROP TABLE #cmd2

        SET @sql  = '-- ------------------------------------------    '+ @cr
        SET @sql += '-- Set Environment Variable to Project Variables '+ @cr
        SET @sql += '-- ------------------------------------------    '+ @cr
        SET @sql =  @tab + 'SET @project = '''+ @name +''';'+ @cr;   
        SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;
        PRINT @sql
        

        SELECT 
             [cmd] =     
                  'RAISERROR(''   Set Project variable ('+ parameter_name  +') ...'', 10, 1) WITH NOWAIT;' + @cr
                + 'EXECUTE [SSISDB].[catalog].set_object_parameter_value '+ @cr
                + @tab + @tab + '@object_type     = '+ CONVERT(NVARCHAR(10), object_type) +',' + @cr
                + @tab + @tab + '@folder_name     = @folder,                    -- Folder (SSIDB >> "FolderName")'+ @cr
                + @tab + @tab + '@project_name    = @project,             -- Environment Name'+ @cr
                + @tab + @tab + '@parameter_name  = N'''+ parameter_name +''',  -- Project Variable Name'+ @cr
                + @tab + @tab + '@parameter_value = N'''+ parameter_name +''',  -- Environment Variable Name'+ @cr
                + @tab + @tab + '@object_name     = N'''+ object_name +''','+ @cr
                + @tab + @tab + '@value_type      = ''R'''+ @cr
            ,[name] = parameter_name
            INTO #cmd2
              FROM [SSISDB].[internal].[object_parameters]
             WHERE project_id = @project_id 
               AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)
               AND parameter_name NOT LIKE 'CM.%'

        /*Print out the variable creation procs */
        WHILE EXISTS (SELECT TOP 1 1 FROM #cmd2)
        BEGIN
                SELECT TOP 1 @sql = cmd, @name = name FROM #cmd2 ORDER BY name;
                PRINT @sql;
               
                DELETE FROM #cmd2 WHERE name = @name;
        END;





    FETCH NEXT FROM db_cursor INTO @project_id, @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 


                   

---------------------------------------------
-- END!
---------------------------------------------
SET @sql = '';


/* finsih the transaction handling */
SET @sql += 'COMMIT TRANSACTION' + @cr;
--SET @sql += 'RAISERROR(N''Complete!'', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N'' _____                       _      _           _ _ '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''/  __ \                     | |    | |         | | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| /  \/ ___  _ __ ___  _ __ | | ___| |_ ___  __| | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| |    / _ \| ''''_ ` _ \| ''''_ \| |/ _ \ __/ _ \/ _` | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| \__/\ (_) | | | | | | |_) | |  __/ ||  __/ (_| |_|'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' \____/\___/|_| |_| |_| .__/|_|\___|\__\___|\__,_(_)'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''                      | |                           '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''                      |_|                           '', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'GOTO EndSave' + @cr + @cr;
SET @sql += 'QuitWithRollback:' + @cr;
SET @sql += 'IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' + @cr;
SET @sql += 'RAISERROR(N''Variable creation failed'', 16,1) WITH NOWAIT;' + @cr + @cr;
SET @sql += 'EndSave:' + @cr;
SET @sql += 'GO';
       
PRINT @sql;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

USE SSISDB;

SET NOCOUNT ON;

-------------------

-- Configuration

-------------------

DECLARE @FolderToExport VARCHAR(50) = 'BI'

DECLARE

@folder_id INT

,@name VARCHAR(150)

,@project_id INT

,@sql VARCHAR(MAX) = ''

,@sqlTemp VARCHAR(MAX) = ''

,@cr CHAR(1) = CHAR(10)

,@tab CHAR(4) = SPACE(4)

,@ProjectName VARCHAR(50)

,@VariableName VARCHAR(150)

,@Value VARCHAR(600)

SET @sql = '';

SET @sql += 'DECLARE @ReturnCode INT=0, @folder_id bigint' + @cr + @cr;

SET @sql += '-- ---------------------------------------------' + @cr;

SET @sql += '--'+ @tab + 'Variable declarations, make any changes here' + @cr;

SET @sql += '-- ---------------------------------------------' + @cr;

SET @sql += 'DECLARE '+ @cr;

SET @sql += @tab + @tab + ' @folder sysname' + @cr;

SET @sql += @tab + @tab + ',@env sysname' + @cr;

SET @sql += @tab + @tab + ',@project sysname;' + @cr;

-- Get Folder ID

SELECT @folder_id = folder_id FROM [internal].[folders] WHERE name = @FolderToExport

PRINT @SQL;

SET @SQL = '';

---------------------------------------------

-- Export Current package Configuration

---------------------------------------------

DECLARE db_cursor_variable CURSOR FOR

SELECT DISTINCT

CONVERT(NVARCHAR(50), projects.name ) AS ProjectName

,CONVERT(NVARCHAR(150), projects.name +'__'+ parameter_name) AS VariableName

,CONVERT(NVARCHAR(600), ISNULL(design_default_value, '') ) AS Value

FROM internal.object_parameters main

INNER JOIN internal.projects ON main.project_id = projects.project_id

WHERE project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] s WHERE s.project_id = main.project_id)

AND parameter_name NOT LIKE 'CM.%'

AND projects.folder_id = @folder_id

ORDER BY CONVERT(NVARCHAR(50), projects.name )

,CONVERT(NVARCHAR(150), projects.name +'__'+ parameter_name)

,CONVERT(NVARCHAR(600), ISNULL(design_default_value, ''))

OPEN db_cursor_variable

FETCH NEXT FROM db_cursor_variable

INTO

@ProjectName

,@VariableName

,@Value

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'DECLARE @'+ @VariableName +' NVARCHAR(600) = N'''+ @Value +''';';

FETCH NEXT FROM db_cursor_variable

INTO

@ProjectName

,@VariableName

,@Value

END

CLOSE db_cursor_variable

DEALLOCATE db_cursor_variable

SET @SQL += 'SET @folder = '''+ @FolderToExport +''''+ @cr + @cr+ @cr;

-- Begin transaction

SET @sql += ';' + @cr + '/* Starting the transaction */' + @cr;

SET @sql += 'BEGIN TRANSACTION' + @cr + @cr + @cr;

----

---- Test if folder exist -> If not create it

--SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder)' + @cr;

--SET @sql += @tab + 'BEGIN' + @cr;

--SET @sql += @tab + @tab + 'RAISERROR(''Creating folder: %s ...'', 10, 1, @folder) WITH NOWAIT;' + @cr;

--SET @sql += @tab + @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_folder] @folder_name=@folder, @folder_id=@folder_id OUTPUT' + @cr;

--SET @sql += @tab + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr;

--SET @sql += @tab + 'END' + @cr + @cr;

---------------------------------------------

-- Create environment + variables

-----------------------------------------------

DECLARE db_cursor CURSOR FOR

SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id

OPEN db_cursor

FETCH NEXT FROM db_cursor

INTO @project_id, @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;

SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE name = @env)' + @cr;

SET @sql += @tab + 'BEGIN' + @cr;

SET @sql += @tab + @tab +'RAISERROR(''Creating Environment: %s'', 10, 1, @env) WITH NOWAIT;' + @cr;

SET @sql += @tab + @tab +'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env' + @cr;

SET @sql += @tab + @tab +'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr + @cr;

SET @sql += @tab + 'END' + @cr;

print @sql;

SET @sql = '';

-- ---------------------------------------------

-- Generate the variable creation

-- ---------------------------------------------

SET @sql += ' -- ProjectId => '+ CONVERT(NVARCHAR(50), @project_id) + @cr;

SELECT DISTINCT [cmd] = @tab + 'RAISERROR('' Creating variable: ' + parameter_name + ' ...'', 10, 1) WITH NOWAIT;' + @cr

+ @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]' + @cr

+ @tab + @tab + ' @variable_name=N''' + parameter_name + '''' + @cr

+ @tab + @tab + ', @sensitive=' + CONVERT(varchar(2), sensitive) + @cr

+ @tab + @tab + ', @description=N''' + [description] + '''' + @cr

+ @tab + @tab + ', @environment_name=@env' + @cr

+ @tab + @tab + ', @folder_name=@folder' + @cr

+ @tab + @tab + ', @value=@' + @name +'__'+ parameter_name + @cr

+ @tab + @tab + ', @data_type=N''' + parameter_data_type + '''' + @cr

+ @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr

, [name] = parameter_name

INTO #cmd

FROM [SSISDB].[internal].[object_parameters]

WHERE project_id = @project_id

AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)

AND parameter_name NOT LIKE 'CM.%'

WHILE EXISTS (SELECT TOP 1 1 FROM #cmd)

BEGIN

SELECT TOP 1 @sql = cmd, @name = name FROM #cmd ORDER BY name;

PRINT @sql;

DELETE FROM #cmd WHERE name = @name;

END;

DROP TABLE #cmd;

SET @sql = '';

FETCH NEXT FROM db_cursor INTO @project_id, @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

---------------------------------------------

-- Set Environment on each Project

-- + Set Environment variables on each Project Variables

---------------------------------------------

DECLARE db_cursor CURSOR FOR

SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id

OPEN db_cursor

FETCH NEXT FROM db_cursor

INTO @project_id, @name

SET @sql = 'DECLARE @reference_id AS BIGINT'+ @cr;

PRINT @sql;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = @tab + 'SET @project = '''+ @name +''';'+ @cr;

SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;

SET @sql += 'RAISERROR(''Configure Project: %s ...'', 10, 1, @project) WITH NOWAIT;' + @cr + @cr

SET @sql += '-- ------------------------------------------ '+ @cr

SET @sql += '-- Set Environment to Project ' + @cr

SET @sql += '-- ------------------------------------------ '+ @cr

SET @sql += 'RAISERROR('' Set Environment (%s) as Reference ...'', 10, 1, @env) WITH NOWAIT;' + @cr

SET @sql += 'EXEC SSISDB.catalog.create_environment_reference'+ @cr

SET @sql += @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")'+ @cr

SET @sql += @tab + @tab + '@environment_name = @env, -- Environment Name '+ @cr

SET @sql += @tab + @tab + '@project_name = @project, -- Project Name '+ @cr

SET @sql += @tab + @tab + '@reference_type = ''R'','+ @cr

SET @sql += @tab + @tab + '@reference_id = @reference_id OUTPUT'+ @cr

PRINT @SQL

-- ------------------------------------------

-- Set Environment Variable to Project Variables

-- ------------------------------------------

IF OBJECT_ID('tempdb..#cmd2') IS NOT NULL DROP TABLE #cmd2

SET @sql = '-- ------------------------------------------ '+ @cr

SET @sql += '-- Set Environment Variable to Project Variables '+ @cr

SET @sql += '-- ------------------------------------------ '+ @cr

SET @sql = @tab + 'SET @project = '''+ @name +''';'+ @cr;

SET @sql += @tab + 'SET @env = '''+ @name +'''' + @cr;

PRINT @sql

SELECT

[cmd] =

'RAISERROR('' Set Project variable ('+ parameter_name +') ...'', 10, 1) WITH NOWAIT;' + @cr

+ 'EXECUTE [SSISDB].[catalog].set_object_parameter_value '+ @cr

+ @tab + @tab + '@object_type = '+ CONVERT(NVARCHAR(10), object_type) +',' + @cr

+ @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")'+ @cr

+ @tab + @tab + '@project_name = @project, -- Environment Name'+ @cr

+ @tab + @tab + '@parameter_name = N'''+ parameter_name +''', -- Project Variable Name'+ @cr

+ @tab + @tab + '@parameter_value = N'''+ parameter_name +''', -- Environment Variable Name'+ @cr

+ @tab + @tab + '@object_name = N'''+ object_name +''','+ @cr

+ @tab + @tab + '@value_type = ''R'''+ @cr

,[name] = parameter_name

INTO #cmd2

FROM [SSISDB].[internal].[object_parameters]

WHERE project_id = @project_id

AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)

AND parameter_name NOT LIKE 'CM.%'

/*Print out the variable creation procs */

WHILE EXISTS (SELECT TOP 1 1 FROM #cmd2)

BEGIN

SELECT TOP 1 @sql = cmd, @name = name FROM #cmd2 ORDER BY name;

PRINT @sql;

DELETE FROM #cmd2 WHERE name = @name;

END;

FETCH NEXT FROM db_cursor INTO @project_id, @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

---------------------------------------------

-- END!

---------------------------------------------

SET @sql = '';

/* finsih the transaction handling */

SET @sql += 'COMMIT TRANSACTION' + @cr;

--SET @sql += 'RAISERROR(N''Complete!'', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N'' _____ _ _ _ _ '', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N''| / \/ ___ _ __ ___ _ __ | | ___| |_ ___ __| | |'', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N''| | / _ \| ''''_ ` _ \| ''''_ \| |/ _ \ __/ _ \/ _` | |'', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N''| \__/\ (_) | | | | | | |_) | | __/ || __/ (_| |_|'', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N'' \____/\___/|_| |_| |_| .__/|_|\___|\__\___|\__,_(_)'', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N'' | | '', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'RAISERROR(N'' |_| '', 10, 1) WITH NOWAIT;' + @cr;

SET @sql += 'GOTO EndSave' + @cr + @cr;

SET @sql += 'QuitWithRollback:' + @cr;

SET @sql += 'IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' + @cr;

SET @sql += 'RAISERROR(N''Variable creation failed'', 16,1) WITH NOWAIT;' + @cr + @cr;

SET @sql += 'EndSave:' + @cr;

SET @sql += 'GO';

PRINT @sql;

Pie Chart is bad!

by Arnaud Gastelblum | 30 Jul 2019 | Power BI

During my presentation at Power Saturday in Paris, I wanted to demonstrate to an already very optimistic public that Power BI is a great tool and that modeling is a must-have for their reports. (Data Modelling is not the subject here)
Power BI a super tool? Yes, and to complete this idea, I added some slides to highlight each piece of the tool.

One of the slides showed the ease and elegance of the visuals.
After a quick search on Google images, I copied a screenshot created by a user in Power BI and added it to my presentation.

In the audience, two friends looked at me with a horrible face. The damage was done, I had slipped the image of a Pie Chart!

Nothing crazy, isn’t it? But when you know a bit of the history of the Pie Chart, we realize that this visual component is not recommended in the world of Data Viz. This blog post also follows some more or less tense discussions between colleagues.

I wrote this article for many reasons:

This point is already super documented on internet, but some people never read them before. (I provide links at the end)
I wanted to convinced myself first, and created a Power BI file to confirm this point. (CFR the following screenshot)

I was a Pie Chart lover

I know that its use is not recommended and I try not to use it.
But yes, I liked it!
• It’s beautiful!
• It’s colorful!
• It gives a professional look to our reports!
• It shows information!
• It gives me some pride to not use tables or matrix. (Yes, I like numbers, not you?)

I think we are used to seeing this kind of visualization since our earliest childhood in commercial brochures, professional documents.

The Pie Chart is visual, and help us to return informations quickly. But unfortunately, this information is not always the right one.

Image Source

Why?

To compare groups, we evaluate the difference of the amplitude of the angles while a histogram will request us to compare lengths between them.

A long description is not needed, I would prefer to show you 2 set of data, both represented in a Pie Chart and histogram.

Your first look will give you some information, but not as much precise compared to the two histograms below. It’s possible to have quick information, but very hard to have a clear interpretation of them.

If I didn’t convince you, try to sort each portion from the smallest to the biggest. Try to do the same with the histogram and compare the time elapsed for both.

https://www.data-to-viz.com/caveat/pie.html

https://medium.com/@clmentviguier/the-hate-of-pie-charts-harms-good-data-visualization-cc7cfed243b6

SSMS – Query Shortcuts : Feel like a superman developer

by Arnaud Gastelblum | 11 Apr 2019 | SQL Server, Tips & Tricks

SSMS Query Shortcut

Dear BI Developer,

I’m pretty sure you would be happy to improve your productivity. If not, you should at least read this article to look like a superman (superwoman) developer.

When I start a new mission, the first thing I do is to set up SSMS (SQL Server Management Studio). And because I’m the kind of guy who acts like a Microsoft BI evangelist (and also for running), I replicate my configuration on my colleague’s machines.

Example

In this example – CTRL + 4 – COUNT

Select statement you want to execute, Press CTRL and 4

SSMS will give you Nb impacted rows.

In BI (and not only!), it’s very important to test if we have unexpected behavior with our joins.

Does our INNER JOIN filter too much data? Or worst, does our join multiply our result set?

A quick and easy CTRL + 4 will ensure you to respect your grain.

How to configure Management Studio

Open Management Studio, Go to Tools > Option…

Under Environment > Keyboard > Query Shortcuts

You have a list of existing shortcuts. (I don’t change them, but I neither use them too).
You should now fill each text box with a query.

(See image and table below)

Queries are available on the next section

Query Shortcuts

Do not forget to add a space after each query.

Tips

CTRL + 3

1000 First Rows

SELECT TOP 1000 * FROM

CTRL + 4

Nb Rows

SELECT COUNT(1) AS Nb FROM

CTRL + 5

All Rows

SELECT * FROM

CTRL + 6

Describe Table

EXEC sp_executesql N' SELECT schemas.name ,tables.name ,columns.name ,types.name ,columns.max_length ,columns.is_nullable ,columns.is_identity FROM sys.tables tables INNER JOIN sys.schemas schemas ON schemas.schema_id = tables.schema_id INNER JOIN sys.all_columns columns ON columns.object_id = tables.object_id INNER JOIN sys.types types ON types.system_type_id = columns.system_type_id WHERE UPPER(RTRIM(LTRIM(tables.name))) = UPPER(RTRIM(LTRIM(REPLACE(REPLACE(@objname, '']'', ''''), ''['', '''')))) ORDER BY tables.object_id, columns.column_id',N'@objname nvarchar(776)', @objname =

Select a table and show a quick description of attributes (Name, Datatype, size, nullable, identity)

CTRL + 0

All Running queries

a better SP_Who! List all running queries (Process ID, Status (blocked or running), users, …)

For more informations check this article

SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = qt.text ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme ,DatetimeSnapshot = GETDATE() FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp WHERE cp.plan_handle = er.plan_handle ) ec OUTER APPLY ( SELECT lead_blocker = 1 FROM master.dbo.sysprocesses sp WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND sp.blocked = 0 AND sp.spid = er.session_id ) lb WHERE er.sql_handle IS NOT NULL AND er.session_id != @@SPID ORDER BY er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id

Power Saturday – Paris

by Arnaud Gastelblum | 6 Apr 2019 | Message

Join us in Paris

You can register on http://powersaturday.com

L’agenda du #PowerSaturday est (presque) finalisé. #Data #PowerBI #O365 #SharePoint #PowerPlatform
Vous pouvez vous inscrire à l’événement : https://t.co/1A9wg84DcL @ClubPowerBI @GUSS_FRANCE @aOSComm pic.twitter.com/V56sdY3eTV

— PowerSaturday (@PowerSaturday) April 18, 2019

« Older Entries

Management Studio – Faster with multiple select

How?

One useful case

SSIS – Create Environment from Packages variables

The following code is not clean, but it does the work! 🙂

Pie Chart is bad!

I wrote this article for many reasons:

I was a Pie Chart lover

Why?

SSMS – Query Shortcuts : Feel like a superman developer

SSMS Query Shortcut

Example

How to configure Management Studio

Query Shortcuts

Do not forget to add a space after each query.

CTRL + 3

1000 First Rows

CTRL + 4

Nb Rows

CTRL + 5

All Rows

CTRL + 6

Describe Table

CTRL + 0

All Running queries

Power Saturday – Paris

Join us in Paris

Recent Posts