InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 01:30:19 GMT20http://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi,I resorted to this in the end (INSERT .. OPENQUERY()) although there are some third party custom controls which handle dynamic column metadata.Seems like a bit of an omission from SSIS as judging from the hundreds of posts on the web a lot of people want to do this and end up becoming rather frustrated.Thanks for your post. Fri, 08 Mar 2013 03:08:44 GMTbill 88428http://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxYou can use the data flow plus component from cosyrock.If you only use on BIDS (and not on sql server agent) it is free. You can then do true ETL stuff with a for each loop to each table).the only thing first is to create the table structure on SQL server or Oracle.There is a nice small turorial here:http://www.cozyroc.com/ssis/data-flow-taskSat, 17 Dec 2011 09:40:31 GMTclementhugehttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxthanks, some kind of code would be helpful. Also in my case even Column list is not defined at design time. So I can have 5 column for say Table1 which needs to be mappped to Destination table (DTable1 say), for next run it could be 4 columns. So i am planning to put the intermidate schema first and then put data into destination. Please suggestWed, 24 Aug 2011 05:31:26 GMTkap_geminihttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxI don't have a copy of the package available now, but do check out the images I posted in one of the replies to this thread. Along with the original explanation, they should give you enough information to build the package quite quickly.Wed, 24 Aug 2011 05:24:29 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxGreat post..Is it possible to share the ssis package. I have the same requirment and would like to experiment with it.In our case source could be Flat files/RDBMsKApilWed, 24 Aug 2011 05:15:07 GMTkap_geminihttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHello AMTZVA,For sql 2000, you can use vbscript to manipulate the dts object model. I've used this technique to generate dts packages for 100s of tables at a time. I used the excel vb environment to write the code, then execute it like a macro. Then this code can be easily ported to a custom vb application. This link should get up started:http://msdn.microsoft.com/en-us/library/aa176227%28v=sql.80%29.aspxYou can learn more about the object model by setting breakpoints and viewing objects in debug mode.Hope this helps,ChadThu, 03 Feb 2011 09:59:18 GMTchad.bindlhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi KiranI don't have the code separately I'm afraid. The thread should contain all the information you need though. It really doesn't take long to create, since there are only a few different tasks you need. Happy to help if you have a specific issue though.NaveedThu, 08 Jul 2010 16:55:36 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi Naveed, Can you send me the example of this code to my email address kiran.nalluri@ingenix.comApprecite your helpThanks!Kiran.Thu, 08 Jul 2010 11:36:09 GMTKiran Chennupati-446386http://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi Your post is really helpful. But is there a way around to use this example without creating linked server object? Any alternate would be helpful.Thanks In Advance.Mon, 21 Jun 2010 00:27:55 GMTVickyWinnerhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi VenaPlease check this link http://munishbansal.wordpress.com/2009/06/09/dynamic-columns-mapping-%E2%80%93-script-component-as-destination-ssis/you will get lot information on dynamic mapping of columns.I hope this will help.Sun, 21 Feb 2010 03:19:14 GMTbrainyhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxhello Grasshopper ,I think I have similar kind of task to do. Actually I have several .txt files(with different number of columns- textfile1 contains about 10 columns and textfile2 contains about 20 columns and textfile3 contains 5 columns and so on).I need to take these text files as source and send these text files data into sql server destination(temportary tables/tables). But the thing is I need to use single flatfile source and single sql server/oledb destination.I tried alot and reached at this stage. I took one for each loop container and configured and created a variable to store .txt filenames. And in the data flow task, Kept one flat file source and one oledb destination.And execute the package. now it is reading all the text files but the problem I am getting is, as my text files have different number of columns mapping i am unable to do dynamically. I try using script task to do it. But i am not able to do that. As you gave the solution to sql server to oracle. I hope you can surely help me.I am really need your help.Thanks alot.vena.Sat, 20 Feb 2010 16:42:27 GMTvithasunhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxIf they're all SQL Server instances then you can create Linked Servers on the Destination server(s) to the Source server(s). Once you've done that, if the destination server is always the same then create a connection to it and amend the INSERT statement to include the server name as well for the source server. If the destination will be on different servers then you can try changing the connection string using Expressions (you'll find this in the Properties against the connection you've created), but I haven't tried it with servers, so not sure if it will work.Thu, 22 Oct 2009 16:29:31 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxThanks guys. My databases will be always on different servers. Will the above solution work? What modification I can do? ThanksSundarThu, 22 Oct 2009 12:03:34 GMTpandiyan70http://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxSSIS can still be used easily, since you're just using it to perform a loop for you that executes a dynamic SQL statement.In your SSIS package you should have at least one OLE DB connection defined. This will connect you to a particular database. Assuming you have permissions to access both databases and that they are on the same server instance, all you need to do is to amend the SQL statement to incorporate the Database name and Schema name:"INSERT INTO [DestinationDB].[dbo]." + @[User::DestinationTable] + " SELECT * FROM [SourceDB].[dbo]." + @[User::SourceTable]This assumes your schema name is dbo, but you can change that as necessary. The other thing you can do, which will make the process dynamic across databases as well, is to add the database and schema names into your source data. So the original table that contains the names will look something like:Source Destination[db1].[dbo].[table1] [db2].[dbo].[table1][db1].[dbo].[table2] [db2].[dbo].[table2][db3].[dbo].[table3] [db4].[dbo].[table3]Here you would go back to the simple form of the SQL statement:"INSERT INTO " + @[User::DestinationTable] + " SELECT * FROM " + @[User::SourceTable]Give me a shout if you need any more help.Thu, 22 Oct 2009 02:39:13 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxI would suggest you to use sql instead of ssis... in sql create a linked server and build the script insert into with select dynamically for all the tables.. that would be a better idea in your case...SSIS is bit difficult in this case.. but if u find any idea to do this dynamically in ssis... i am curious to know...Wed, 21 Oct 2009 14:56:28 GMTbrainyhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi Naveed,I am working on a similar issue. There are 2 SQL Server databases. I need to transfer all the data from tables in source DB to target DB tables after truncating the target tables. I did not setup LinkedServer because I do not need to do. I followed every thing. But what is confusing to me is Step 5. How can write a query like this in it.INSERT INTO [target db]..[target table] select * from [source db]..[source table]Do I need to create a connectionstring variable for source? what default value I can give it to this variable?Do I need to select the target connection in the "connection" in SQL Statement section of the "Execute SQL Task Editior"?ThanksSundarWed, 21 Oct 2009 12:56:50 GMTpandiyan70http://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxAmtzvaThis is all specific to SSIS which didn't appear until SQL Server 2005. In SQL Server 2000 you can use DTS and make things dynamic too, but requires a completely different method.Thu, 29 Jan 2009 16:06:23 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxThe images are good... can I use this in sqlserver 2000?thank you very much by your helpAMTZVAThu, 29 Jan 2009 15:01:34 GMTamtzvahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxThanks naveed.Really it is helpful.Thu, 29 Jan 2009 11:52:13 GMTbrainyhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxI have attached some images that may help.Thu, 29 Jan 2009 04:57:33 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHi.I was reading your article and it is very interesting. Can you send me a graphic example?Thank you for your timeThu, 22 Jan 2009 08:31:53 GMTamtzvahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxThanks Naveed...I will try this.Mon, 05 Jan 2009 06:47:52 GMTbrainyhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxHiYou won't be able to use the Bulk Insert Task because that only applies to files and not database connections. So if you want to use the Execute SQL task, you can do the following (this may not be the ideal way of doing it, but it works):SUMMARY:Read in the list of tables to variables -> Use a ForEach Loop container to read through each of these and amend an Execute SQL task on each loop -> Make the Execute SQL task do a simple TSQL insert.DETAILS:1. Set up a Linked Server (Server Objects > Linked Servers) to the source DB (if you can't use one of SQL Server's built-in providers then you can set up an ODBC connection and then connect to that). 2. In SSIS create a variable of type Object3. Create a task that reads in the list of tables you need and maps to the variable. For example, you could have a database table that contains two columns such as SourceTable and DestinationTable. In that case use an Execute SQL task to do a simple select on that table, but make sure you then map that Result Set to the object variable. 4. Create variables to hold each value. So in the example above, create one variable to hold the SourceTable name and one to hold the destination one.4. Create a ForEach Loop container. Edit the container and on the Collection section ensure the Enumerator is set to ForEach ADO Enumerator and you select the Object variable in the section below. Then go to the Variable Mappings and map each output from your select statement earlier to your variables. So for example, if your select statement in step 3 outputs 2 columns, then in the mappings section under variables select your SourceTable variable and under Index enter 0, and similarly select you DestinationTable variable and Index 1. So now your variables will contain the table names you need on each loop.5. Place an Execute SQL task inside the container. Edit the task and on the Expressions section, add an Expression for SqlStatementSource. Then give it a value of something like:"INSERT INTO " + @[User::DestinationTable] + " SELECT * FROM OPENQUERY(XXXXXXX, 'SELECT * FROM " + @[User::SourceTable] + "')"Here the XXXXX will be the name of the Linked Server you set up at the start. Of course, you can test the statement first in a normal SQL Query window to make sure it works first:INSERT INTO AAAAAAASELECT * FROM OPENQUERY(XXXXXX, 'SELECT * FROM BBBBBB')where AAAAA is your destination table and BBBBB is your source table.One final note - if you can read directly from the source DB from SQL Server (e.g. if it is just another SQL Server DB), then you don't need to do step 1, and in the final step your SQL query will be even simpler, i.e. of the form:INSERT INTO AAAAASELECT * FROM BBBBBBHope that helps. Give me a shout if you still get stuckMon, 05 Jan 2009 05:52:18 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspx[quote]We had a similar problem, although it was using text files and other ODBC connections as the source. I also looked through the web, and unfortunately as far as i know, the answer is you can't do it in a Data Flow Task.If however, you try using a Bulk Insert Task, or using an execute SQL task, you can certainly make those dynamic (you can use expressions to change the connection strings, etc). If you need more help on how to do this, let me know.[/quote]Can you please post the solution which u feel its working... If it is feasible I will use that method only.Fri, 02 Jan 2009 05:49:45 GMTbrainyhttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxWe had a similar problem, although it was using text files and other ODBC connections as the source. I also looked through the web, and unfortunately as far as i know, the answer is you can't do it in a Data Flow Task. If however, you try using a Bulk Insert Task, or using an execute SQL task, you can certainly make those dynamic (you can use expressions to change the connection strings, etc). If you need more help on how to do this, let me know.Fri, 02 Jan 2009 04:03:12 GMTnaveed.khawajahttp://www.sqlservercentral.com/Forums/Topic628424-147-1.aspxCan any body let me know how to map the columns dynamically in ssis ....Suppose I have some 10 tables in a database.. I need to export the data in these tables to tables in oracle database.I need to migrate data from sql to oracle. Since the method is same for all tables I think we can make it dynamic so that for each table it should migrate the data.I searched throughout the net but I could not able to find a method for this. Can anybody let me know how we can achieve this dynamically.Table schema in sql and oracle are same. How can I do this?Thu, 01 Jan 2009 06:36:08 GMTbrainy