Closure of the MS BI project

will write some experience through this project
Several items that I’d like to touch are listed below:

1. SSIS tips
Development
We have 3 types of packages: daily, weekly and monthly.
Daily pkgs will pick up new records everyday and aggregate into the summary table.
For daily changed reference data, we do not pick up untill the monthly pkgs kick in.
That’s our version to Slowly Changing Dimension (ideally, SCD should be used)

weekly pkgs will truncate the fact table and bring 3 month new records

monthly pkgs will truncate dimension tables and bring all records

Deployment
So far, the deployment is using file system and import pkgs via SSMS. That’s not the best way though but DBA feels more comfortable in this way

Debugging
We have created a sysssislog table which contains all log information. If data error or pkg error occurs , dba or am will get an email immediately.

When we direct error output to another table in OLE DB destination, make sure it’s using table/view instead of fast load. If fast load is used, if the stream hits any bad data, ssis will direct all data after the bad row into the error table no matter if it’s bad or not. Be careful!

2. SSAS tips
Development
Deployment
Debugging

3. Version Control (SVN)

Dynamic connection manager in ssis packages

@[System::MachineName] is the right way to put in the expression of connection manager. The property is ServerName. In this way, when pkgs are executed , it will automatically use the servername which will save tons of time on the dba’s side.

 

Want to change several columns to NULL

I have a few columns in three tables and want to convert them to nullable column. Below is the script

 

DECLARE @tbl_name varchar(255)
DECLARE @col_name varchar(255)
DECLARE @dt_type varchar(255)
DECLARE @alterCommand varchar(255)

if exists (
select
table_name,
column_name ,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'NO'
and TABLE_NAME like '%ERR'
)
begin

DECLARE TblCursor CURSOR FOR

select
table_name,
column_name ,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'NO'
and TABLE_NAME like '%ERR'

OPEN TblCursor
FETCH next FROM TblCursor
INTO @tbl_name,@col_name,@dt_type

WHILE @@fetch_status=0
BEGIN
    SET @altercommand = 'alter table ' + @tbl_name +
    ' alter column '+@col_name+' '+@dt_type+' null '
    EXECUTE(@dropcommand)
    FETCH next FROM TblCursor INTO @tbl_name,@col_name,@dt_type
end

end
CLOSE TblCursor
DEALLOCATE TblCursor