Hi guys, I have a execute sql task which selects table names from sys.tables. I want these tables names to get passed into the data flow task where I want a oledb source to take these tables one by one and load into a different destination. For this I created a package with execute sql task and I took the full result set into a object variable. I placed a data flow task in the for each loop container and all I want is I need the data flow task to take each table name into a variable and do the data flow? would this be possible in SSIS?

Hi, 1--Execute sql task, full result Set. that will save in object type variable ( Select name from sys.tables). 2--Foreach loop, Select ADO Enumurator and provide object type variable you have used in Execute SQL Task above. 3--create a variable in Variable Mapping during configuring Foreach Loop. 4-- USe thise New Variable, lets say VarTableName as parameter in OLE DB Source you are using in Data Flow. or you can use this variable in an other variable to build the query and finally use that variable in your Data Flow task as Query Source. Helpful links: http://www.codeproject.com/KB/database/foreachadossis.aspx ADO Enumrator.. Almost same pattern you need to use. Another one http://www.bidn.com/blogs/MikeDavis/ssis/150/ssis-for-each-ado-enumerator-loop Thankshttp://sqlage.blogspot.com/

Steve, you will probably struggle with this because SSIS needs to know the metadata for the table you are loading to / from in advance. It's not possible to dynamically build at runtime source and target within a data flow. (unless using Cozyroc extensions). James Beresford @ www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex

I agree with BI Monkey. It will be better done with T-SQL-Script and Linked Server if possible or load the data to Ascii-files and import them at the Destination Server.

Hi Steve, since you want to transfer data from a dynamic list of tables, you can write a basic SQL statement which contains both select and insert inside an Execute SQL ask with the sql statement in a query and the SqlSourceType as variable. Sample Query : "insert into "+ @[User::TableDST] + " select * from "+ @[User::TableSRC] you can take the corresponding table names from the foreachloop variables/collection let me know if worked

Sorry to ask you, I am seriously new to this term Cozyroc extensions. Can you help me with this?

Hello, Here is website http://www.cozyroc.com/ They have custom transformations that you can use for your packages. I believe so you have to purchase or get register to download their product. Once you get it then you install it and finally add in BIDS to use them. Instead of getting into this all , why don't you write a SP and call that in your package. please provide following information 1--Do you want to get all the table from sys.tables or few of them? 2--Do you want to select all the columns from each table and finally load to destination? 3-- How do you know that , which source need to be loaded to which destination? 4--Do you have a list of columns that you want to select for each source and load in separate destination? Thankshttp://sqlage.blogspot.com/

Hi Aamir, I need 10 tables to get refreshed nightly. Yes I need to select all the columns. The tables are already there in QA environment which are taken from production. Can you suggest me how can I write a SP/

Hello, please check the script, i have created a temp table on the top, which has sourcetable Name and Destination Table Name. You can create permanent table or even you can follow the same thing and populate this table with your required source and destination table names. I assume that Source and Destination tables has the same number of columns. DECLARE @TableName table (SourceTableName VARCHAR(300), DestinationTableName VARCHAR(300)) INSERT INTO @TableName Values ('TSource','TDestination') INSERT INTO @TableName Values ('TSource1','TDestination1') --SELECT * from @TableName -----Get Table Names from Sys.tables------ DECLARE @SourceName VARCHAR(300) DECLARE @DestinationName VARCHAR(300) DECLARE Cur_TableName CURSOR FOR SELECT SourceTableName,DestinationTableName From @TableName OPEN Cur_TableName FETCH NEXT FROM Cur_TableName INTO @SourceName,@DestinationName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @COLUMNS VARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) SET @COLUMNS='' SET @SQL='' SELECT @COLUMNS= COALESCE(@COLUMNS + ', ', '') + Name FROM sys.columns WHERE object_id=object_id(@SourceName) SET @COLUMNS=SUBSTRING(@COLUMNS,2,LEN(@COLUMNS)) SET @SQL='INSERT INTO DBO.'+@DestinationName+' SELECT '+@COLUMNS+ ' FROM '+@SourceName Print @COLUMNS Print @SQL EXEC (@SQL) FETCH NEXT FROM Cur_TableName INTO @SourceName,@DestinationName END CLOSE Cur_TableName DEALLOCATE Cur_TableName http://sqlage.blogspot.com/

Thanks aamir, I will try out this and will let you know. Do you want me to use this in a SSIS package or can I schedule it directly in a job?

One more simple way to do this.. you said you have only TEN Tables and i am sure the table names does not change everyday. So create a package , Bring Data flow task in Control flow pane. 1--Get OLE DB Source, Write your query for first table( SELECT * from dbo.Table1 or Select col1,col2,col3 from dbo.TABLe1) , Drag OLE DB Destination and then map the columns from source to destination. repeat Step1 for all the rest of Nine Tables. You can have separte Data Flow task for each of them or you can put all in one Data Flow task. You can attach Data Flow task in parallel or You can connect them in sequence , all depends upon your choice. Thankshttp://sqlage.blogspot.com/

You can use just in SP as you are not using funcationaly of SSIS( like logging, configuration etc). please read above post if you still want to do with SSIS. Thanks http://sqlage.blogspot.com/

Thanks man that done the trick.