https://wikitech.raphaelbrenet.com/ 2026-06-07T20:43:56+00:00 https://wikitech.raphaelbrenet.com/ https://wikitech.raphaelbrenet.com/lib/exe/fetch.php?media=wiki:dokuwiki.svg text/html 2022-05-02T14:09:09+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_data_dictionary&rev=1651500549&do=diff SQL Server queries for data dictionary Tables List tables with schema, modification date, number of rows and columns, comments: object_id schema_name table_name create_date modify_date nb_rows nb_columns comments text/html 2022-05-02T10:07:54+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=ssis:ssis_run_sql_queries_in_script_task&rev=1651486074&do=diff SSIS - Run SQL queries from a Script Task Using OLEDB connection Run a query that returns data (SELECT): string connectionString = Dts.Connections["SqlServerConnectionName"].ConnectionString; string queryString = "SELECT col, COUNT(*) FROM schema.MyTable GROUP BY col" using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand command = new OleDbCommand(queryString, connection); connection.Open(); OleDbDataReader reader = command.ExecuteReader(); while (re… text/html 2022-05-02T09:57:40+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=start&rev=1651485460&do=diff Welcome This is a wiki for tips regarding business intelligence and database tools. Power BI: * Calendar Table * Power Query M snippets * Tabular Editor Script Snippets SSIS * SSIS SQL query on Excel file * SSIS Display info from script task/component (FireInformation) * SSIS Run SQL queries in Script Task SQL Server * SQL Server data dictionary * SQL Server parse JSON * SQL Server Queries to generate queries * SQL Server See sessions and running queries * SQL … text/html 2022-02-12T11:31:24+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_queries_to_generate_queries&rev=1644665484&do=diff SQL Server queries to generate queries Count rows in all tables Check all tables from a DB schema, with table name starting with D_: DECLARE @schema_name varchar(50); SET @schema_name = 'dwh'; SELECT queryCount = STUFF(( SELECT 'UNION ALL SELECT ''' + @schema_name + '.' + name + ''' as TblName, (SELECT count(*) FROM ' + @schema_name + '.' + name + ') as NbRows ' FROM sys.tables WHERE schema_id = SCHEMA_ID(@schema_name) AND name like 'D[_]%' FOR XML PATH ('') ), 1, 10, '') text/html 2022-02-12T11:06:08+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=ssis:ssis_display_info_from_script_task_component_fireinformation&rev=1644663968&do=diff SSIS - Raise event: FireInformation Display info from the script task in the output logs: bool fireAgain = true; Dts.Events.FireInformation(0, "Script Task Example", "Internet connection detected.", String.Empty, 0, ref fireAgain); Display info from the script text/html 2021-02-04T17:17:11+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_recover_dropped_objects_tables_views&rev=1612459031&do=diff SQL Server Recover dropped objects The query below will display the CREATE scripts for dropped object on the specified database and time period DECLARE @Database_Name NVARCHAR(MAX)= 'My_DB_Name' ,@Date_From DATETIME='2021-02-04' ,@Date_To DATETIME ='2021-02-05' ,@Compatibility_Level INT SELECT @Compatibility_Level = dtb.compatibility_level FROM master.sys.databases AS dtb WHERE dtb.name=@Database_Name IF ISNULL(@Compatibility_Level,0)<=80 BEGIN RAISERROR('The compatibility level sh… text/html 2021-01-13T16:06:38+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=ssis:ssis_sql_query_on_excel_file&rev=1610553998&do=diff SSIS - SQL query on Excel file The general syntax is as follow: SELECT * FROM [SheetName$Range] WHERE condition If the option “first columns contains headers” is specified in the connection, the column names available in the query will be the ones from the Excel sheet. Otherwise, the dataset columns will be named F1, F2, F3 text/html 2020-11-24T18:02:33+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=powerbi:tabular_editor_script_snippets&rev=1606240953&do=diff References: * Advanced Scripting Reference * Useful snippets Model management Hide the column with the specified name (here “date”) in all selected tables: foreach(var t in Selected.Tables) { foreach(var c in t.Columns) { if(c.Name == "Date") { c.IsHidden = true; } } } text/html 2020-10-16T09:54:16+00:00 Raphael (rafadmin@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_see_sessions_and_running_queries&rev=1602842056&do=diff SQL Server Sessions and running queries SELECT SessionId = ses.session_id ,[Database] = DB_Name(er.database_id) ,[Login] = ses.login_name ,Host = ses.host_name ,StartTime = er.start_time ,ClientAddress = con.client_net_address ,SQLStatement = st.text FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections…