Feeds:
Posts
Comments

SSIS – Learning

This is an ongoing post that will be appended with new additions.

In a SQL Task the “Parameter Mapping” and the SQL executed must be in SYNC else SSIS cannot complete the task and it stays Waiting (yellow colour) and will NOT throw an error. For example if you are passing a parameter the query needs to have a a question mark.

 

Advertisements

TSQL / SSIS – Snippets


--SSIS > Script Task > Message Box
------------------------------------
            string strMessage = Dts.Variables["User::strAlterCmd"].Value.ToString();
            MessageBox.Show(strMessage);

--ALTER TABLE CHECK
-----------------------

IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID

('dbo.usama_mdlDataLake_Behaviour')
				AND name in ('carBreakdown'))		-- UA:You will either 

have all or none so just check any one column

--Check if Index exists / Create
-------------------------------
CONSTRAINT PK_tbl_ComplaintExtracts_LoadID PRIMARY KEY CLUSTERED (LoadID)

IF NOT EXISTS (SELECT top 1 *  FROM sys.indexes  WHERE name = 'ix_churn1')

--TempDB:
	IF NOT EXISTS (SELECT top 1 * FROM tempdb.sys.indexes  WHERE name = 'ix_ChurnIndex1') 

create unique clustered index ix_ChurnIndex1 on #t1(customerid)

CREATE NONCLUSTERED INDEX [IX_tbl_BGL_Transactions_FileName] ON [dbo].[tbl_BGL_Transactions] 

([PolicyNumber])

 IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name'
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
DROP INDEX ix_tblSVV_Permissions_Orchid_CustomerID ON [dbo].[tblSVV_Permissions]

--Table Exists
--------------
IF NOT EXISTS (SELECT top 10 * FROM INFORMATION_SCHEMA.TABLES
   	WHERE TABLE_NAME = 

'DataLoad' -- do not include square brackets
			AND TABLE_SCHEMA = 'stg')
BEGIN       --
  PRINT 'Table Exists'
END

--VIEW Exists
------------------
IF EXISTS(select top 1 * FROM sys.views where name = 'myV')
DROP VIEW myV

--Drop all temp tables
--------------------------

DECLARE @DropTableSQL varchar(max)
SELECT @DropTableSQL = ISNULL(@DropTableSQL + ';', '') + 'drop table ' + SUBSTRING(t.name, 1, 

CHARINDEX('___', t.name) - 1)
FROM tempdb..sysobjects AS t
WHERE t.name LIKE '#%[_][_][_]%'
	AND t.id = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1));
PRINT @DropTableSQL
--EXEC (@DropTableSQL)


--Try catch block
----------------------
BEGIN TRY
DECLARE @MyInt INT, @flag bit = 1  

-- the error state is custom number given so that a developer can locate where the error occured
--http://dba.stackexchange.com/questions/35893/what-is-error-state-in-sql-server-and-how-it-can-be-used

	if @flag = 0
		RAISERROR ('my custom error message', 16, 1); -- the error state a number 
	else 
		SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT @flag flag
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH;
GO 


You can passs temp table inside a dynamic sql and it will process that for you


declare @s varchar(200)

if object_id('tempdb..#t') is not null 
drop table #t

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)
	'

exec (@s)

select * from #t -- seccond time

 

File Recovery from NAS HDD

I had a HDD taken out of my NAS – it was the “My Book World Edition II (White Light)”. Although the HDD was fine but the NAS was loosing its connectivity capabilities day by day and eventually stopped working one day.

I successfully took the HDD  out by following the instructions in the below link:

http://www.instructables.com/id/Disassembling-a-Western-Digital-My-Book/

Using varieties of free software from within Windows 10 I discovered that it was partitioned with Linux file format (Ext4 and RAW file formats). I have tried more software but I was not being able to see my folders and files but rather some other folders and files that I did not recognise (these were the files and folders used by the NAS).

After much looking further I stumbled upon http://www.reclaime.com/.

I tried the trial software and within a few clicks I could see all my files and folders. I was amazed to able to browse the folders and files that belonged to me. I was also really amazed at how easy it was to use the software.

I went ahead and bought the software. Although it was pricey (£190.93) it exactly did what it said on the tin.

 

In this example we will be using a Tablix as the objects but really it could be a Matrix, Graphs, Textbox etc.

Assume a scenario. In Tablix A (on the left hand side) we have a list of fruits :
Melons and Apples

If we click on Melons then on a second Tablix B (on the right hand side) we will display the colours Yellow and Green. If apples is selected then we will show Red and Green.

Create a Parameter called DrillThrough of type Text and make the visibility Hidden.

Goto the textbox property of the cell holding Tablix A’s values.
This is where the magic happens:
Select Action.
From there select “Go to report”
Under the Specify a Report select the name of the report you are currently on (this is so that it refer’s back to itself).
Then click the “Add” parameter button.
Select the parameter DrillThrough and under value select the field from Tablix A.

You can now test if you wanted using a text box by creating a text box and in the expression put in =Parameters!DrillThrough.Value
Delete the text box if yout testing is complete.

Finally create a Dataset where the parameter DrillThrough is used so that depending on what is clicked the list of Fruits in Tablix A that will be sent through to display the list of relevant colours in Tablix B.


-- List of days in the week
;WITH WeekNames
AS
(
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate

    UNION ALL 

    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
)
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
as
(
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
)
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
as
(
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
)
SELECT YearNumber
FROM Last10Years
ORDER BY YearNumber DESC 






/*
    There are many uses of date tables and it could becomes essential at other times.
    The below script creates a function that produces a daily date table with the passed start and end dates.

    There are also examples at ehe ver bottom
*/

create function [dbo].[udf_DailyDateTable]
      (@StartDate datetime, @EndDate datetime)         
returns @DailyDates table 
(
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int
)
AS 
BEGIN
 --   ------------ Test -------------

	---- select * from [dbo].[udf_DailyDateTable] ('1 jan 2016', '5 jan 2016')

 --   declare @DailyDates table
 --   (
 --       DailyDatesID int identity,
	--   DailyDate date, 
	--   DayNumber int,
 --       DayName varchar(50),
 --       WeekNumber int,
	--   QuaterNumber int,
	--   MonthNumber int,
	--   MonthCalled varchar(50),
 --       YearNumber int
 --   )
     
 --   declare @StartDate date, @EndDate date
 --   set @StartDate = '15 sep 2010' -- GETDATE()
 --   set @EndDate = '19 nov 2011'
	
 --  ------------------------ 
 
    while (@StartDate <= @EndDate)
    begin
        insert into @DailyDates
        select	@StartDate DailyDate
			 , datename(day, @StartDate) DayName 
				, datename(dw, @StartDate) DayName 
                , datepart(week, @StartDate) WeekNumber
			 , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
				, datename(month,@StartDate) MonthCalled
				, YEAR(@StartDate) YearNumber
         
        set @StartDate = dateadd(day, 1 ,@StartDate)
        --print @StartDate -- test ` 
    end
     
    --select * from @DailyDates -- test ` 
    return 
END


------------------------ Examples below ------------------------------ 

DECLARE @DateToProcess date = '28 jul 2016' -- getdate() -- this is generally todays date 

-- it is important to initialse @EndOfLastMonth first because that in turn is used in @YTDDateFrom. 
-- if this is run in january then the last year's dates will be used  
DECLARE @EndOfLastMonth date = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@DateToProcess) , 0)) -- End of previous month. All results should be upto this date not yesterday
DECLARE @YTDDateFrom date = cast('1 jan ' + str(year(@EndOfLastMonth)) as date) -- YTD stars from 1 Jan. Just add the requested year


-- MTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(@EndOfLastMonth)
						  

-- QTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.QuaterNumber = datepart(q,@EndOfLastMonth) 

-- YTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.YearNumber = datepart(yy,@DateToProcess) 

-- Yesterday 
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.DailyDate = dateadd(day, -1, @DateToProcess)

-- Month Of Yesterday
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess))


---- e.g. ------
/*
    ---------- Filter for dates ----------------
    and (CASE 
		  WHEN ddt.DailyDate = dateadd(day, -1, @DateToProcess) THEN 'Yes'  --- this and the next ones need to be before the otheres 
		  WHEN ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess)) THEN 'MoY' -- this may be becasue thiss is a derived table and @DateToProcess > @EndOfLastMonth
		  WHEN ddt.MonthNumber = month(@EndOfLastMonth) THEN 'MTD' 
		  WHEN ddt.QuaterNumber = datepart(q,@EndOfLastMonth) THEN 'QTD' 
		  WHEN ddt.YearNumber = datepart(yy,@DateToProcess) THEN 'YTD'   		  
    END ) = @XTD 
    --------------------------------------------
*/
------------