Dan's BI Braindump

SSIS Config using SQL tables

You need a SQL db e.g. SSIS_Config

 

Then create this table…

 

create

table SSIS_Configurations

(

 ConfigurationFilter

VARCHAR(100) NOT NULL,

 PackagePath

NVARCHAR(255) NOT NULL,

 ConfiguredValueType

NVARCHAR(20) NOT NULL,

 ConfiguredValue

NVARCHAR(1000) NULL

)

 

Create an environment system variable called eg. SSIS_ROOT

Enter the connection string to the above db into the value

eg. Data Source=.\SQL2008; Initial Catalog=SSIS_Config; Provider=SQLNCLI10; Integrated Security=SSPI;Auto Translate=False

 

Create new SSIS package

 

Create OLEDB connection called Configuration – point to SSIS_Config db

 

Create a new SSIS configuration…

 

Type = Environment Variable

Variable = SSIS_ROOT

 

Next > Select ConnectionString property of Configuration connection manager

 

See http://msdn.microsoft.com/en-us/library/cc671625.aspx for more info

 

Using UNPIVOT in SQL Server

Today I had to write a procedure to unpivot a table with an unknown number of columns / column names, I’ve used a combination of user defined function and dynamic sql to build a sql unpivot statement and then execute it…

First create function…

CREATE FUNCTION [dbo].[Udfgetcolumnnames] (@Table VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
  BEGIN
      DECLARE @str VARCHAR(MAX)

      SET @str =”

      SELECT @str = @str + column_name + ‘,’ frominformation_schema.columns
      WHERE  table_name = @Table
             AND column_name NOT IN( ‘ShiftStart’, ‘AgentName’, ‘Date’ )

      SET @str =Substring(@str, 1, Len(@str) – 1)

      RETURN @str
  END  

Then use it to help define the SQL…

DECLARE @sql NVARCHAR(MAX)

SET @sql =”
SET @sql =
‘SELECT AgentName,  Date, pvt.Interval, pvt.ActivityCode FROM ActualAgentActivity UNPIVOT ( ActivityCode for Interval in (‘ +
dbo.Udfgetcolumnnames(‘ActualAgentActivity’) + ‘)  ) as pvt WHERE pvt.ActivityCode <> ”.”’

EXEC Sp_executesql @sql 

You will notice I hardcoded some values in my function – Trying to add this functionality as an extra parameter/s was a complete nightmare and I ran out of time.  If anyone has a suggestion on how this could be made more generic I would love to hear it (given that there could be any number of excluded columns)

Getting a sharepoint page to refresh automatically

<script type="text/javascript" language="javascript">
var reloadTimer = null;
var sURL = unescape(window.location.pathname);

function setReloadTime(secs)
{ if (arguments.length == 1)
   { if (reloadTimer) clearTimeout(reloadTimer);
       reloadTimer = setTimeout("setReloadTime()", Math.ceil(parseFloat(secs)*1000));
   }
   else
   { reloadTimer = null;
     location.reload(true);
     window.location.replace( sURL );
   }
}

setReloadTime(30);
</script>

Thanks for this go to http://drewmace.blogspot.com/2008/02/auto-refresh-sharepoint-page.html 

Instant SQL formatter

Getting the definition of a view out of oracle

I know I’ll have to do this again one day so….

 

SQL> set long 10000

SQL> select TEXT
  2  FROM DBA_VIEWS
  3  where OWNER = ‘<owner_name>’
  4  and VIEW_NAME  = ‘<view_name>’;

 

Credits to http://technopark02.blogspot.com/2007/05/oracle-how-to-get-tableview-definition.html  

How to add a calculated measure to SSAS cube using XMLA