Hi, i have one table with some data and i want to copy into different tables based on below condition if in table year=2010 then it should go in 1table , likewise for 2011 and that tables should get dynamically created whenever if i insert new record it should directly create a folder if that particular year is not in a table can any one suggest me how can i do it vandana

Hi Vandana, There is muliple options: Insert using Execute SQL Task with appropriate where clauseUse Conditional Split Transformation by creating cases that suit your need(here YEAR value)Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

there is no simple way to do this in SSIS as DFT doesn't support the dynamic metadata. Work Around : you have to use script task to check the new data (2012 or 2013) and create table there only... and even load this data there... --Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Hi Raunak, Thanks for the reply If i use conditional split i dont know how many years will come in feature then how conditional split works in my casevandana

Following can be a possible solution: 1) Query the source table to get a list of all unique "year" 2) Enumerate over this list in a for each container. Within the for each container: a) Check if a table of name "year" value exists in the database. Use this as reference on how to do it. b) Using precedence constraints, if the table does not exist create a new table c) Load data from the source to the destination table using a data flow taskhttp://btsbee.wordpress.com/

HI btsbee, could you please provide detail stepsvandana

Step 1) Get unique year. Used below sql in execure sql task. SELECT DISTINCT(RIGHT(CONVERT(VARCHAR(10), <DateColumn>, 105), 4)) FROM <TableName> Save this result in a variable of type object. 2) Use a for each container with an ado enumerator and specify the above variable. Under variable mappings specify a variable of string type which will hold the table name. a) In the foreach container first check if table exsits. Use the below sql SELECT COALESCE((SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = ?), -1) Specify the table name variable under variable mappings and under result set specify a variable of integer type to hold the result. b) Use precedence constraints i.e. connections with Expression and Contraints to direct the flows. Something like: The only difference being instead of Not found script component you will have another execute sql task to create the table and then you would have data flow task to load the data. http://btsbee.wordpress.com/