最近2個月的work

------------- 2個月工作

1.ssis 包 資料遷移

2.mysql windows replication slave 制作

3. mssql  procedure

---- ddl   trigger

create table xwj_loong_update_version

(id bigint identity(1,1) ,

 EventType sysname,

 ObjectName sysname,

 PostTime char(10),

 CommandText nvarchar(1000),

 UserName   sysname,

 eventdata xml

)

;

alter trigger tr_table_event on database

for ddl_table_events

as

declare

 @eventdata xml

,@PostTime char(10)

,@EventType sysname

,@ObjectName sysname

,@ObjectType sysname

,@CommandText  nvarchar(1000)

,@UserName sysname

select @eventdata=eventdata();

select @EventType=t.c.value('(EventType)[1]','sysname')

   ,@ObjectName=t.c.value('(ObjectName)[1]','sysname')

   ,@PostTime=convert(char(10),t.c.value('(PostTime)[1]','char(10)'),120)

   ,@CommandText=t.c.value('(TSQLCommand/CommandText)[1]','nvarchar(1000)')

      ,@UserName=t.c.value('(UserName)[1]','sysname')

 from @eventdata.nodes('EVENT_INSTANCE') as t(c)

if @EventType=N'CREATE_TABLE'

begin

 if @ObjectName not like 'xwj%'

 begin

  raiserror('not allowed to create this type table',16,1)

  rollback

  insert into xwj_loong_update_version(EventType,ObjectName,PostTime,CommandText,UserName,eventdata)

   select @EventType,@ObjectName,@PostTime,@CommandText,@UserName,@eventdata

 end

end

if @EventType=N'DROP_TABLE'

begin

 if @ObjectName like 'xwj%'

 begin

  raiserror('not allowed to drop this type table',16,1)

  rollback

 end

end

go

--- check db version

if user_id(N'p_check_version','U') is not null

drop procedure p_check_version;

create procedure p_check_version

(@dbname nvarchar(100)

,@checktype tinyint=1    ----- full 1  tabledata 2

)

as

begin

 declare

  @database sysname

 ,@num tinyint;

 if object_id(N'tempdb..#','U') is not null

 drop table #;

 with xwj

  as

  (select id=1

   union all

   select id+1 from xwj where id<101

  )

 select px=identity(int,1,1),substring(dbname,id,charindex(',',dbname+',',id)-id)

  into #

  from (select @dbname as dbname) x1

  inner join xwj as x2 on  len(x1.dbname)>=id and substring(','+dbname,id,1)=','

  option(maxrecursion 0);

 create clustered index in_#_id on #(id);

 select top(1) @dataname=dbname from # ;

 while @@rowcount>0

 begin

  select @[email protected]+1

 end

Ssis  運用于 mysql 5.1_37

Mysql 字元集最好是utf8(可以再 my.ini /my.cnf  中更改)

Mysql connecter 5.1 (mysql 5.1 odbc ）使用 datareader 

String=driver={myssqlodbc3.51driver};server=%servername%;database=%database%,uid=%username%;password=%password%

或者 本機建立 dns 

一般 正常的資料etl ods 都可以用ssis 來實作

Data flow 

Control flow

The others

Mysql replication 

Grant replication slave  on *.*  to replicusr1@'slaveip 'identified by '123456' 

在從機上面 cmd 

Mysql -hreplicationip -ureplicuser1 -p123456 能否登陸

Replication 

My.ini 

Server-id=1

Log-bin=mysql-bin.log

Binlog-do-db=db1

Binlog-do-db=db2

Expire_logs_days=3

Restore  mysql server

Show master status

---- 清空日志

Flush tables with read lock

Reset master 

Buckup data -------move data to  slave 

Stop mysql server

---------------------------------

Slave 

My.ini

Server-id=2

Master-host=replicationip

Master-user=replicuser1

Master-password=123456

Master-port=3306

Masetr-coonect-retry=60

Replicate-do-db=db1

Replicate-do-db=db2

Slave-skip-errors=all

Report-host=replicationip

Expire_logs_days=7

Stop slave mysql server

Start replication server

Start slave server 

Slave server

Start slave 

Show slave status

Slave_io =yes

Running_sql yes

總結 : ssis 很強大,也想有更多的資料遷移項目去實踐，乃至 ssas ssrs 和 ssis 配套使用.

mysql 在 windwos 上真 不适合跑複雜的業務 邏輯.