Quick Tip: Suspend/Resume All ADF V1 Pipelines

In Dev and QA environments, it is occasionally necessary to pause all Azure Data Factory V1 pipelines. PowerShell makes quick work of an otherwise manual task. Login-AzureRmAccount Select-AzureRmSubscription -SubscriptionId “PUT-Y0UR-SUBSCR1PT10N-1D-H3R3” $RGName = “ResourceGroupName” $DFName = “DataFactoryName” $pipelines = Get-AzureRmDataFactoryPipeline -DataFactoryName $DFName -ResourceGroupName $RGName foreach( $pl in $pipelines) { Suspend-AzureRmDataFactoryPipeline -DataFactoryName $DFName -ResourceGroupName $RGName -Name $pl.PipelineName #Resume-AzureRmDataFactoryPipeline -DataFactoryName $DFName -ResourceGroupName $RGName -Name $pl.PipelineName }

Create Data “Thumbnail” in Azure Data Lake Store

Recently, a colleague could not create a model in Power BI Desktop because the dataset files were too large. The smallest file was 45GB. He asked if we could rerun the Azure Data Lake Analytics job (U-SQL) to create a smaller dataset. We could, however one of the jobs that creates the datasets runs for about four hours. The solution I came up with was to create a dataset “thumbnail” which would provide a small […]

Finding Column Definitions in Oracle, SQL Server and DB2

In order to speed up the development of Azure Data Factory pipelines, I created a table containing definitions of various databases tables. Each of the big three databases provide a method for querying table and column definitions. SQL Server select table_catalog, table_schema, table_name, column_name, ordinal_position, data_type, coalesce(character_maximum_length,numeric_precision) as column_length from information_schema.columns Oracle select ‘myOracleDatabase’ as databasename, owner, table_name, column_name, column_id, data_type, data_length from all_tab_columns DB2 select ‘myDB2Database’ as databasename, tabschema, tabname, colname, colno, typename, length […]

SQL Server: Kill All the SPIDs

Sometimes, we need to take a SQL Server database offline. After notifying the users and giving them plenty of warning, they leave connections to the database – some connections are SLEEPING or AWAITING COMMAND, others have select statements that have been running for over four hours. I use the following sql to kill off all of the remaining user sessions on a database. Just change the YOURDATABASE text to the name of your database and […]

BI Publisher Report Shows ‘No Data Found’

A report in Oracle BI Publisher Enterprise v11.1.1 returned “No data found.” for all elements in the report when viewed by guests and administrative users. The report used several data sources and only worked for the sysman user. The problem data source turned out to be the default data source, EMREPOS, which stores all of the Oracle Enterprise Manager data and for this installation is also the BI Publisher repository. EMREPOS is a hidden data […]