Should we separate the ssis packages between sever

问题:

I use SSIS2012. I have created three schema in my Data warehouse(STG, TRSF, DW).

The STG schema is for staging tables. All my source file are the CSV files. I am transferring the data from my source to each table in stg schema. I have a separate package for each tables (For example: If i have 20 csv files, I will have 20 packages and i will populate 20 tables in stg schema)

After that, I am transferring stg schema to trsf schema. During those process i have my business. I do lookup for FK and other business rules will be applied in this level. The same as privious example if I have 20 tables in stg schema, I will have 20 packages and I will fill 20 tables in trsf schema.

In third step I will transfer the data from trsf schema to dw schema. Here also I have 20 packages.

At end, I will have 20 package for cleaning the tables in stg schema.

I create 4 packages for each transformation between csv files and the tables in dw schema.

I would like to know if it is good idea to create 4 separate projects for each step? Because now, I have 80 packages in one project and it can be grow up more.

回答1:

I would keep the projects together. However to separate them, I would keep separate parent jobs for Landing, Staging and the Data Warehouse. And you could control these Parent jobs by a Master Parent jobs..This would give you freedom to run individual stages independently.

Another suggestion, I would suggest you separate the Landing, Staging and the Data Warehouse in separate databases. Believe me it would help you in the long run.

Cheers Nithin