Hello. If I have a type 3 scd - a column is added to the dimension - and I implement a script component that executes when a new column is required such that the script component adds the column to the SQL table hosting the dimension; then this brings the output data table schema in the ssis package out of sync with the back end, so in theory the process would break. How does one resolve this, or is there a better way to approach this situation? Thanks, Nick.

Check here http://munishbansal.wordpress.com/2009/06/09/dynamic-columns-mapping-%E2%80%93-script-component-as-destination-ssis/http://uk.linkedin.com/in/ramjaddu

I'm not too sure how that's "dynamic" at all - everything is hard-coded. Typically with SSIS, you can't be changing metadata - the number of columns or their data types. It's intended to be used with static tables. The only real way to make it dynamic is to build packages through the API, where you use code to construct the package and can therefore define a brand new package for how the table looks each time. John Welch's post on advanced SCD techniques may have something for you in the downloads. Talk to me now on

1) Yes, whenever there are column additons are removal in the data flow mapping, the metadata has to be reinitialized. 2) http://msdn.microsoft.com/en-us/library/ms135954.aspx.Happy to help! Please mark the post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

Thank you to everyone who answered this question. I see that it is actually my fault in misunderstanding the SCD3 type and that this issue is not related to an SCD 3 type at all. The issue stems from trying to have SSIS dynamically create new columns in a table on new pivoted data, which in some respects is lazy (toot my own horn here!). A clearer definition of the type of information that the end user requires based on the information being channelled into the dataflow, matching them with requirements is really what is needed. The aim here is to have SSIS create new columns in a table based on new values in rows in the data, which opens some degree of trouble when maintaining the application, so: So, for example, There could be 5 rows for transaction A101221, each row is duplicated except for two columns; ADC Field and ADC Value. In row1: "Number of Eggs", "24"; row 2: "Number of Raw Eggs", 12; "Amount Of Flour", "1 KG" ... So, the dimension table would be ID, Amount Of Eggs, Amount Of Raw Eggs, Amount Of Flour ... When new ADC Field values are added to the "possible" list of ADC Field Values, then the dimensional OLTP table changes to incorporate the new field. When this happens the metadata for the table changes, and the SSIS package breaks because of this. So, lets say that the source application suddenly gets a new ADC Field Label called "Number Of People in Party", and the data in Transaction B201778 reflects that field with a value of say, 6; the dimension needs to add a new column called "Number Of People In Party" which changes the schema of the underlying table, and therefore invalidates the dataflow in the SSIS component. This then involves modifying the SSIS package to reflect this change, not to mention modifying the Analysis services database metadata and so on ... I was wondering if anyone could put forward a way of making this more automatic, reducing the workload but still maintain the same useability for the end user. I am seeing the knock-on effect that my design is having on the work that it would take to keep this going if the administrator (who is the only person that can add Fields to the source system), is going to add values to the data ad-hoc. Maybe the Star schema needs re-engineering? I am considering snowflaking in this instance, although I am trying not too. I wonder if anyone has come accross this same issue, and what they did to resolve it. Thanks, Nick.

Thank you to everyone who answered this question. I see that it is actually my fault in misunderstanding the SCD3 type and that this issue is not related to an SCD 3 type at all. The issue stems from trying to have SSIS dynamically create new columns in a table on new pivoted data, which in some respects is lazy (toot my own horn here!). A clearer definition of the type of information that the end user requires based on the information being channelled into the dataflow, matching them with requirements is really what is needed. The aim here is to have SSIS create new columns in a table based on new values in rows in the data, which opens some degree of trouble when maintaining the application, so: So, for example, There could be 5 rows for transaction A101221, each row is duplicated except for two columns; ADC Field and ADC Value. In row1: "Number of Eggs", "24"; row 2: "Number of Raw Eggs", 12; "Amount Of Flour", "1 KG" ... So, the dimension table would be ID, Amount Of Eggs, Amount Of Raw Eggs, Amount Of Flour ... When new ADC Field values are added to the "possible" list of ADC Field Values, then the dimensional OLTP table changes to incorporate the new field. When this happens the metadata for the table changes, and the SSIS package breaks because of this. So, lets say that the source application suddenly gets a new ADC Field Label called "Number Of People in Party", and the data in Transaction B201778 reflects that field with a value of say, 6; the dimension needs to add a new column called "Number Of People In Party" which changes the schema of the underlying table, and therefore invalidates the dataflow in the SSIS component. This then involves modifying the SSIS package to reflect this change, not to mention modifying the Analysis services database metadata and so on ... I was wondering if anyone could put forward a way of making this more automatic, reducing the workload but still maintain the same useability for the end user. I am seeing the knock-on effect that my design is having on the work that it would take to keep this going if the administrator (who is the only person that can add Fields to the source system), is going to add values to the data ad-hoc. Maybe the Star schema needs re-engineering? I am considering snowflaking in this instance, although I am trying not too. I wonder if anyone has come accross this same issue, and what they did to resolve it. Thanks, Nick.