Type 2 dimension table layout

In order to track Type 2 dimension processing, metadata columns are required.  Below is an example.

SSIS 2012 Multi-column hash key transformation

Have you ever needed to do data comparisons over many columns and desire to use a hash key algorithm?  And needed to do it in SSIS?

I invite you to look at the information at CodePlex:

http://ssismhash.codeplex.com/documentation?referringTitle=Home


This is unbelievable stuff and it should be noted they're the authors of said code and documents.  Just thought it was great enough to share.

How to pass a variable to an SSIS package on execute?

Q:  How to pass a variable to an SSIS package on execute?

A:  A few ways.  One can execute via a command line and designate the variable value, execute via SQL Agent and pass the value or use a config file.  I'm going to demo the 1st two below.

Execute via SQL Agent:

Or via command line:
*note: this example uses a SSIS package stored in a file folder.

/FILE "C:\SSIS\projects\SSIS_PKG.dtsx"  /CHECKPOINTING OFF /SET "\Package.Variables[User::myvar].Properties[Value]";myvalue /REPORTING E

SQL Server Database Backup Script

Looking for a quick database backup script with a datetimestamp?  Replace the [DBName] with your database name.  Note the [FolderPath] needs to be updated as well.

SQL Server Database Backup Script with datetimestamp:



--db name   [DBName]

--use master db for backups
use master

--variables
declare @year varchar(4)set @year=datepart(yy,getdate())
declare @month varchar(2)set @month=case when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as varchar(1)) else cast(datepart(mm,getdate())as varchar) end
declare @day varchar(2)set @day=case when datepart(dd,getdate())<10 then '0'+cast(datepart(dd,getdate()) as varchar(1)) else cast(datepart(dd,getdate())as varchar) end
declare @hour varchar(2) set @hour=case when datepart(hh,getdate())<10 then '0'+cast(datepart(hh,getdate()) as varchar(1)) else cast(datepart(hh,getdate())as varchar) end
declare @minute varchar(2)set @minute=case when datepart(mi,getdate())<10 then '0'+cast(datepart(mi,getdate()) as varchar(1)) else cast(datepart(mi,getdate())as varchar) end

--create bkp device
declare @sqlscript varchar(2000)
set @sqlscript='exec sp_addumpdevice ''disk'', ''[DBName]_networkdevice'', ''[FolderPath]\[DBNAME]'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

--bkp db
BACKUP DATABASE [DBName] TO [DBName_networkdevice] WITH  INIT ,  NOUNLOAD ,  RETAINDAYS = 5,  NAME = N'[DBName backup',  NOSKIP ,  STATS = 10,  DESCRIPTION = N'[DBName] backup',  NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='[DBName]'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='[DBName]')
RESTORE VERIFYONLY FROM  [[DBName]_networkdevice]  WITH FILE = @i

--drop bkp device
exec sp_dropdevice '[DBName]_networkdevice'

Execute SSIS package from command line for 32bit

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\MSDB\[FOLDER]\[PACKAGE NAME]" /SERVER [SERVER NAME] /DECRYPT [PASSWORD] /X86 /CHECKPOINTING OFF /REPORTING E

Row Number: Quick TSQL Row_Number

Create a quick row number in a sql statement:

select ROW_NUMBER() OVER(ORDER BY [COLUMN] ASC/DESC) AS Row
,* from [TABLE])

ETL Framework Stages

ETL Framework Stages:

STAGE:    storage area between the source data and the data warehouse or ODS or BI marts.  typically temporary in nature.  Used for data cleansing, landing of data in a like format and placing the data out of its source format.

PERSISTENT STAGE:  storage area of data that allows transactional and incrementally changing data to be stored.  Typically the data is kept close to its originating structure and is not related to other sources as one would see in a dimensional approach or 3NF ODS.  It is not used as a system or record, but rather a processing area for historical storage  Meta data columns help process changes to keep history and the likes of type 2 (or others) disciplines can be applied.

ODS:  An operational data store (ODS) is designed to integrate data from multiple sources. The data used then as the system of record and will be used to update/insert data back out to source systems.

DW:  The data warehouse (DW) is a database used for reporting and analysis. It acts as a repository of data that can be fed from STAGE directly, PERSISTENT STAGE (for historical purposes), or the ODS . Data warehouses typically contain current and historical data.

BI MART (DATA MART):  A focused slice of the data warehouse built to focus on a specific subject area.  It can be separated from the DW to help with storage, security, or further business logic not desired in the DW.

SP_WHO to see SQL statement

Quick query to see user and full SQLstatement.  Don't forget one can use the Profile from SSMS for a full view of all db activity.


SELECT  D.text SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) Status,
A.login_name Login, A.host_name HostName, C.BlkBy,  DB_NAME(B.Database_ID) DBName,
B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime, ISNULL((B.reads + B.writes),
(A.reads + A.writes)) DiskIO,  A.last_request_start_time LastBatch, A.program_name FROM
   sys.dm_exec_sessions A    LEFT JOIN    sys.dm_exec_requests B  
 ON A.session_id = B.session_id   LEFT JOIN    
 (        SELECT                 A.request_session_id SPID,        
       B.blocking_session_id BlkBy        
  FROM sys.dm_tran_locks as A          
 INNER JOIN sys.dm_os_waiting_tasks as B        
  ON A.lock_owner_address = B.resource_address        ) C
  ON A.Session_ID = C.SPID   OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

Rank Over

,RANK() OVER
    (PARTITION BY col] ORDER BY [colA] desc, [colB] desc) AS Rank

from MSN  (http://msdn.microsoft.com/en-us/library/ms176102.aspx):

USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO

SQL Server DB extract and restore from Litespeed

declare @extractor varchar(4000)
, @fullbkp_filename varchar(4000)
, @fullbkp_filepath varchar(4000)
, @ext_path varchar(4000)
,@database varchar(100)
,@fullrestore varchar(4000)
,@datafilepath varchar(500)
,@logfilepath varchar(500)
set @ext_path='R:\Litespeed'
set @fullbkp_filepath='R:\Litespeed\archive_bkps'
set @fullbkp_filename='ods_stage_201211011253.bak'
set @database='ods_stage'
set @datafilepath='R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data'
set @logfilepath='L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs'

--EXTRACTOR
set @extractor='xp_cmdshell '''+@ext_path+'\extractor.exe -E  '+@fullbkp_filepath+'\'+@fullbkp_filename+' -F '+@fullbkp_filepath+'\'+@fullbkp_filename+''''
exec (@extractor)

--FULL
set @fullrestore='
RESTORE DATABASE '+@database+'
FROM  DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'0''
,DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'1''
,DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'2''
with replace
,  MOVE N'''+@database+''+'_data'' TO N'''+@datafilepath+'\'+@database+'_data.mdf''
,  MOVE N'''+@database+''+'_log'' TO N'''+@logfilepath+'\'+@database+'_log.ldf''
,norecovery'
exec (@fullrestore)

--DIFF (enable reocvery after last diff apply)
RESTORE DATABASE [scratch]
FROM  DISK = N'R:\LiteSpeed\U1048487_201210311424.bak'
with file=2
--toggle to norecovery if need to apply another diff
, recovery


--toggle to norecovery if need to apply a diff
--,norecovery

/*
,  NOUNLOAD,  STATS = 10*/

/*
RESTORE DATABASE [scratch] FROM  DISK = N'R:\LiteSpeed\scratch_2012102108151.bak' WITH  FILE = 2
,  MOVE N'scratch' TO N'R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\scratch.mdf'
,  MOVE N'scratch_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs\scratch.ldf',  NOUNLOAD,  STATS = 10

RESTORE DATABASE [scratch] FROM  DISK = N'R:\LiteSpeed\scratch_2012102108152.bak' WITH  FILE = 3
,  MOVE N'scratch' TO N'R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\scratch.mdf'
,  MOVE N'scratch_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs\scratch.ldf',  NOUNLOAD,  STATS = 10*/

quick db restore

RESTORE DATABASE em700
FROM  DISK = N'C:\em700201211161241.BKP'

with replace

Quick sql server db bkp

--db name   em700

--use master db for backups
use master

--variables
declare @year varchar(4)set @year=datepart(yy,getdate())
declare @month varchar(2)set @month=case when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as varchar(1)) else cast(datepart(mm,getdate())as varchar) end
declare @day varchar(2)set @day=case when datepart(dd,getdate())<10 then '0'+cast(datepart(dd,getdate()) as varchar(1)) else cast(datepart(dd,getdate())as varchar) end
declare @hour varchar(2) set @hour=case when datepart(hh,getdate())<10 then '0'+cast(datepart(hh,getdate()) as varchar(1)) else cast(datepart(hh,getdate())as varchar) end
declare @minute varchar(2)set @minute=case when datepart(mi,getdate())<10 then '0'+cast(datepart(mi,getdate()) as varchar(1)) else cast(datepart(mi,getdate())as varchar) end

--create bkp device
declare @sqlscript varchar(2000)
set @sqlscript='exec sp_addumpdevice ''disk'', ''em700_networkdevice'', ''\\udrsql11\C$\em700'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

--bkp db
BACKUP DATABASE [em700] TO [em700_networkdevice] WITH  INIT ,  NOUNLOAD ,  RETAINDAYS = 5,  NAME = N'em700 backup',  NOSKIP ,  STATS = 10,  DESCRIPTION = N'em700 backup',  NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='em700'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='em700')
RESTORE VERIFYONLY FROM  [em700_networkdevice]  WITH FILE = @i

--drop bkp device
exec sp_dropdevice 'em700_networkdevice'

Moving User databases

-->Moving user databases

-->Detach the database as follows
use master
   go
   sp_detach_db 'db'
   go

-->Next, copy the data files and the log files from the current location
-->(D:\Mssql7\Data) to the new location (E:\Sqldata).

-->Re-attach the database
use master
  go
  sp_attach_db 'db',
'H:\MSSQL\db1.mdf',
'H:\MSSQL\db2.mdf',
'H:\MSSQL\db3.mdf',
'I:\MSSQL\db4.mdf',
'I:\MSSQL\db5.mdf',
'I:\MSSQL\db6.mdf',
'N:\MSSQL\db_log.ldf'
--'N:\MSSQL\db_log2.ldf'
  go

-->Verify

use db
   go
   sp_helpfile
   go

-->source: http://support.microsoft.com/kb/224071
-->source for tempdb:


use master
go
Alter database tempdb modify file (name = tempdev, filename = 'K:\mssql\tempdev.mdf')
Alter database tempdb modify file (name = tempdev2, filename = 'K:\mssql\tempdev2.mdf')
Alter database tempdb modify file (name = tempdev3, filename = 'K:\mssql\tempdev3.mdf')
Alter database tempdb modify file (name = tempdev4, filename = 'K:\mssql\tempdev4.mdf')
Alter database tempdb modify file (name = tempdev5, filename = 'K:\mssql\tempdev5.mdf')
Alter database tempdb modify file (name = tempdev7, filename = 'K:\mssql\tempdev7.mdf')


go
Alter database tempdb modify file (name = templog, filename = 'J:\mssql\templog.ldf')
go

Update DB recovery model

--VIEW

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'model' ;
GO

--CHANGE

USE master ;
ALTER DATABASE model SET RECOVERY FULL /*SIMPLE*/

What is Predictive Analytics and how does it relate to BI?

The "predictive" approach stems from modeling data and storing in such a way an analyst/statistician can use it to make those decisions "openly" rather than boxing a decision maker into a silo of data which can limit them. You'll see a tool such as SAS, SPSS (and there are other approaches) used to allow a person to perform"what-ifs" that'll evolve the data to help them drive to conclusions.

Traditional BI reporting does a fine job at aggregating and showing how a company has done to date but lacks the ability to make decisions on what one should do. Predictive analytics builds on the practice of data mining and the principles of building decisions on what's in front of a company based on existing data, market conditions and the driving conclusions of model scores.

db backups

USE [Operations]
GO

/****** Object:  StoredProcedure [dbo].[db_backups]    Script Date: 04/05/2011 15:13:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec db_backups 'model'
CREATE procedure [dbo].[db_backups]
@db_name varchar(50)
AS
-->declare working variables
DECLARE
@db nvarchar(255),
@cleanup varchar(4000),
@device nvarchar(4000),
@ddevice nvarchar(4000),
@backup varchar(4000),
@bkp_path varchar(1000),
@bkp_device varchar(1000),
@bkp_l_device varchar(2000),
@bkp_sql nvarchar(4000)

-->@bkp_l_device default setting
set @bkp_l_device=null

-->bkp file cleaner
declare cleaner cursor  for select bkp_partition_path  from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where [db_name] =(''+@db_name+'')
order by 1

OPEN cleaner

FETCH NEXT FROM cleaner INTO @bkp_path
WHILE @@FETCH_STATUS = 0
BEGIN
-->purge old backup files.  full purge, no dynamic bkp file names
SET @cleanup='del /Q ' + '"'+@bkp_path+''+ @db_name + '*.bak"'
PRINT @cleanup
exec master..xp_cmdshell @cleanup
FETCH NEXT FROM cleaner INTO @bkp_path
END
CLOSE cleaner
DEALLOCATE cleaner

-->build backup device(s)
declare devices cursor  for select BP.bkp_partition_id  from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where BM.[db_name] =(''+@db_name+'')
order by 1

OPEN devices

FETCH NEXT FROM devices INTO @bkp_device
WHILE @@FETCH_STATUS = 0
BEGIN
-->build bkp devices
set @bkp_path=(select  bkp_partition_path from operations.dbo.ADMIN_db_bkp_partitions BP
where bkp_partition_id=@bkp_device)


SET @device='exec master..sp_addumpdevice ''disk'', ''bkp_'+@db_name+'_device_'+@bkp_device+''', '''+@bkp_path+''+@db_name++@bkp_device+'.bak'''--+@bkp_device+
print @device

SET @bkp_l_device = --N'EXECUTE ' + @proc_name
CASE WHEN @bkp_l_device IS NOT NULL
THEN @bkp_l_device + ',' + 'bkp_'+@db_name+'_device_'+@bkp_device+''
ELSE 'bkp_'+@db_name+'_device_'+@bkp_device+''
END
exec sp_executesql @device
FETCH NEXT FROM devices INTO @bkp_device
END
CLOSE devices
DEALLOCATE devices
print @bkp_l_device

-->backup the database
set @bkp_sql='BACKUP DATABASE ['+@db_name+'] TO
'+@bkp_l_device+' WITH NOFORMAT, INIT,
NAME = N'''+@db_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
print @bkp_sql
exec sp_executesql @bkp_sql

-->drop backup device(s)
declare ddevices cursor  for select BP.bkp_partition_id  from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where BM.[db_name] =(''+@db_name+'')
order by 1

OPEN ddevices

FETCH NEXT FROM ddevices INTO @bkp_device
WHILE @@FETCH_STATUS = 0
BEGIN
-->drop bkp devices

SET @ddevice='exec sp_dropdevice bkp_'+@db_name+'_device_'+@bkp_device+''
print @ddevice

exec sp_executesql @ddevice
FETCH NEXT FROM ddevices INTO @bkp_device
END
CLOSE ddevices
DEALLOCATE ddevices


/*scripts for meta tables

CREATE TABLE operations.[dbo].[ADMIN_db_bkp_meta](
[db_name] [varchar](50) NOT NULL,
[bkp_partition_id] [int] NOT NULL
) ON [PRIMARY]


CREATE TABLE operations.[dbo].[ADMIN_db_bkp_partitions](
[bkp_partition_id] [int] NOT NULL,
[bkp_partition_path] [varchar](max) NOT NULL
) ON [PRIMARY]

insert ADMIN_db_bkp_partitions
(bkp_partition_id,bkp_partition_path)
values(1,
'\\HQFINSQL90\E$\backup\')

insert ADMIN_db_bkp_meta
(db_name,bkp_partition_id)
values ('model','1')
*/
GO

SQL Server: create table indexes from meta data

How to create a meta data driven table index routine using T-SQL.

1)  Metadata container


CREATE TABLE [dbo].[ETL_INDEXES](
[TABLE_NAME] [varchar](100) NOT NULL,
[INDEX_TYPE] [varchar](2) NOT NULL,
[INDEX_NAME] [varchar](100) NOT NULL,
[DROP_FL] [bit] NOT NULL,
[CREATE_FL] [bit] NOT NULL,
[REBUILD_FL] [bit] NOT NULL,
[COLUMNS] [varchar](max) NOT NULL
) ON [PRIMARY]

2)  Create T SQL routine to loop through meta data table based on passed table variable


CREATE PROCEDURE [dbo].[sp_loop_create_table_indexes]
@table_name varchar(100)
AS

/*
@table_name: target table

*/
-->This stored procedure will read meta data stored in etl_indexes
--and perform creates where flagged

--*declare working variables
DECLARE @cursor CURSOR
DECLARE @vTABLE_NAME varchar(100)
DECLARE @vINDEX_TYPE varchar(2)
DECLARE @vINDEX_NAME varchar(100)
DECLARE @vDROP_FL bit
DECLARE @vCREATE_FL bit
DECLARE @vREBUILD_FL bit
DECLARE @vCOLUMNS varchar(max)

SET @cursor  = CURSOR FOR
Select distinct TABLE_NAME
, INDEX_TYPE
, INDEX_NAME
, DROP_FL
, CREATE_FL
, REBUILD_FL
, COLUMNS
from operations.dbo.ETL_INDEXES where TABLE_NAME=@table_name
OPEN @cursor

FETCH NEXT FROM @cursor INTO  @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS

WHILE @@FETCH_STATUS <> -1
BEGIN

exec dbo.sp_create_table_indexes @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS

FETCH NEXT FROM @cursor INTO @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS
END
CLOSE @cursor  
DEALLOCATE @cursor
GO

3) Create T SQL routine to create indexes (sub routine to previous step)


CREATE PROCEDURE [dbo].[sp_create_table_indexes]
@table_name varchar(100)
,@index_type varchar(2)
,@index_name varchar(100)
,@drop_fl bit
,@create_fl bit
,@rebuild_fl bit
,@columns varchar(max)

AS

/*
@table_name: target table name
@index_type: IX (nonclustered), CX (clustered), UX (unique nonclustered),
PK (primary key constraint, unique clustered)
**NOTE: PK's can only be applied to non-null columns, there can be only
one clustered index, by default PK's are clustered with this script
@index_name: Name of table index
@drop_fl: triggers index drop
@create_fl: triggers index creation
@rebuild_fl: triggers index rebuild
@columns: list of columns to index
*/

-->This stored procedure executes a create index based on passed variables

--*declare working variables
declare @PK varchar(4000)
declare @IX varchar(4000)
declare @CX varchar(4000)
declare @UX varchar(4000)

IF @index_type='PK' BEGIN
set @PK='
ALTER TABLE [dbo].['+@table_name+'] ADD  CONSTRAINT ['+@index_name+'] PRIMARY KEY CLUSTERED
('+@columns+') WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@PK)
END

IF @index_type ='IX' BEGIN
SET @IX='
CREATE NONCLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@IX)
END

IF @index_type ='UX' BEGIN
SET @UX='
CREATE UNIQUE NONCLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@UX)
END

IF @index_type ='CX' BEGIN
SET @CX='
CREATE CLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@CX)
END

4)  Create T SQL drop index procedure (this is sub routine to steps above)


CREATE PROCEDURE [dbo].[sp_drop_table_indexes]
@table_name varchar(100)
,@index_type varchar(2)
,@index_name varchar(100)
,@drop_fl bit
,@create_fl bit
,@rebuild_fl bit
,@columns varchar(max)

AS

/*
@table_name: target table name
@index_type: IX (nonclustered), CX (clustered), UX (unique nonclustered),
PK (primary key constraint, unique clustered)
@index_name: Name of table index
@drop_fl: triggers index drop
@create_fl: triggers index creation
@rebuild_fl: triggers index rebuild
@columns: list of columns to index
*/

-->This stored procedure executes a drop index based on passed variables

--*declare working variables
declare @PK varchar(4000)
declare @I varchar(4000)

IF @index_type='PK' BEGIN
set @PK='
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N'''+@index_name+''')
ALTER TABLE [dbo].['+@table_name+'] DROP CONSTRAINT ['+@index_name+']
'
exec (@PK)
END

IF @index_type IN ('IX','UX','CX') BEGIN
SET @I='
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N'''+@index_name+''')
DROP INDEX ['+@index_name+'] ON [dbo].['+@table_name+'] WITH ( ONLINE = OFF )'
exec (@I)
END

Informatica: dynamic filelist generation from metadata

Purpose: How to build dynamic filelist for Informatica.
Required:  File and Source metadata

Step1:  Create Informatica mapping.
Step2:  Drop source(s) in mapping that will drive unique filename, source name, filelist name.  One example would be building a filelist of files to load that have not been logged (loaded in ETL system) or run but logged a failure (re-run due to failure).
Step3: I suggest using an aggregator post joiner (if used) to get a list of unique filenames, source names, filelist names.
Step4: Add a sort transform to put data in order by filelist name
Step5: Add function transform to generate output columns.
Needed: full file path (from variable) appended to filename (ex: $$landing_dir||'/'||FILENAME).
Add 3 ports.
vNEW_FILENAME_IND (var): IIF(FILELIST_NAME = vPREV_FILELIST_NAME,0,1)
vPREV_FILELIST_NAME (var): FILELIST_NAME 
NEW_FILENAME_IND (output port): vNEW_FILENAME_IND
Step6: Add transaction control transform
Map the filename+full_path port, New Filename Ind port, Filelist name port
For the TX control condition: IIF(NEW_FILENAME_IND=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)
Step7:  Map to Flat file target
Filelist name port and Filename+path port

Unix shell: preps meta load to a logging repository. collects file meta.

#!/bin/bash/

#ADMIN_fileprep_sh

#preps meta load to a logging repository.  collects file meta.

#Set variables
vPath=/source_directory
nPipe="|"
nVar="##_"

#Set file perms
#chmod -f 666 $vPath/ADMIN_filelist_prep
chmod -f 666 $vPath/ADMIN_filelist_rename

#Rename files and insert into Admin_filelist_prep
while read line
do
mv $vPath/$line $vPath/$nVar`date +"%Y%m%d%H%M%S"`_$line
echo "$nVar""`date +"%Y%m%d%H%M%S"`_$line">>$vPath/ADMIN_filelist_prep
done<$vPath/ADMIN_filelist_rename

#Loop for permissions
while read line
do
chmod -f 666 $vPath/$line
done<$vPath/ADMIN_filelist_prep

#Loop for dos2unix conversions, uncomment if desired
#while read line
#do
#isitdos='cat $vPath/$line|head -1| od -c|grep "\r"|wc -l'
#if [$isitdos > 0]
#then
#dos2unix -k $vPath/$line
#fi
#done<$vPath/ADMIN_filelist_prep

#Create filestats
>$vPath/ADMIN_filestats

#Loop for stats
while read line
do
     nCount=`wc -l<$vPath/$line`
     nBytes=`wc -c<$vPath/$line`
     nDate=`date -r $vPath/$line +%F`
     echo "$line""$nPipe""$nCount""$nPipe""$nBytes""$nPipe""$nDate">>$vPath/ADMIN_filestats
done<$vPath/ADMIN_filelist_prep

#Loop for Bytes
#while read line
#do
#     nBytes=`wc -c<$vPath/$line`
#     echo "$nBytes" "$line">>$vPath/ADMIN_filebytes
#done<$vPath/ADMIN_filelist_prep

#Loop for Dates
#while read line
#do
#     nDate=`date -r $vPath/$line +%F`
#     echo "$line" "$nDate" >>$vPath/ADMIN_filedates
#done<$vPath/ADMIN_filelist_prep

#Adjust permissions for files
#chmod -f 666 $vPath/ADMIN_filestats
#chmod -f 666 $vPath/ADMIN_filebytes
#chmod -f 666 $vPath/ADMIN_filedates

#Move files to prep folder
#while read line
#do
#mv $vPath/$line $vPath/prep/$line
#done<$vPath/ADMIN_filelist_prep