Data Engineering Technical Blog

How can we deploy SSIS in Azure Data Factory V2 ? With Azure Data Factory V2 (Preview) you can deploy integration services packages easily and schedule them. Prerequisite : Azure Subscription (You can use free trials subscription) Steps to deploy SSIS in Azure 1. Login to azure portal and go to menu button and click on new as shown 2. You can also search for Data Factory from the search box and click on it, you will be redirected to data factory creation page as below. 3. Fill in all the details (Version should be V2 (Preview)) and click on create. 4. Once the data factory is created you can navigate to it and click on the Autor & monitor widget as below. 5. Now you will be redirected to authoring page where you will get multiple options like Create pipeline, copy data, configure SSIS runtime. Since we want to deploy SSIS packe we need to configure the SSIS runtime. Click on the same as shown below. 6. Fill in the desired details on the configuration on the run tim...

How we can monitor memory usage by a SQL Server instance ? -- We don't need the row count SET NOCOUNT ON -- Get size of SQL Server Page in bytes DECLARE @pg_size INT, @Instancename varchar(50) SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' -- Extract perfmon counters to a temporary table IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters -- Get SQL Server instance name SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio' -- Print Memory usage details PRINT '----------------------------------------------------------------------------------------------------' PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion...

Scenario : Find the list of procedures which contains the text - "datafinder" Solution : To get the procedure with text content we need to query two tables - sys.procedures and sys.Sql_modules. The query to get all the procedure containing text - "datafinder" is below SELECT [Name] FROM sys.procedures AS A INNER JOIN sys.sql_modules AS B ON A.object_id = B.object_id AND B.definition like '%datafinder%' Explanation : The sys.sql_modules keep the definition details of any sql module like view, procedure or function by object_id and sys.procedure contains the procedure with its object_id. Since we are specifically looking for procedure containing a specific text so we joined sys.sql_modules with sys.procedures. Similarly we can do the same for SQL Server views as shown below. SELECT [Name],definition FROM sys.views AS A INNER JOIN sys.sql_modules AS B ON A.object_id = B.object_id AND B.definition like '%datafinder%' Or f...

1. Install SQL Server features In the SQL Server installation wizard, on the  Feature Selection  page, select the following items: ·         Database Engine Services ·         Integration Services o    Scale Out Master o    Scale Out Worker On the  Server Configuration  page, click  Next  to accept the default service accounts and startup types. On the  Database Engine Configuration  page, select  Mixed Mode  and click  Add Current User . On the  Integration Services Scale Out Configuration - Master Node  and  Integration Services Scale Out Configuration - Worker Node  pages, click  Next  to accept the default settings for the port and certificates. Finish the SQL Server installation Wizard. 2. Install SQL Server Management Studio Download and install  SQL Server Management Studio ...