January 1, 2009 at 6:36 am
Can 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?
January 2, 2009 at 4:03 am
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.
January 2, 2009 at 5:49 am
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.
Can you please post the solution which u feel its working... If it is feasible I will use that method only.
January 5, 2009 at 5:52 am
Hi
You 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 Object
3. 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 AAAAAAA
SELECT * 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 AAAAA
SELECT * FROM BBBBBB
Hope that helps. Give me a shout if you still get stuck
January 5, 2009 at 6:47 am
Thanks Naveed...
I will try this.
January 22, 2009 at 8:31 am
Hi.
I was reading your article and it is very interesting. Can you send me a graphic example?
Thank you for your time
January 29, 2009 at 4:57 am
I have attached some images that may help.
January 29, 2009 at 11:52 am
Thanks naveed.
Really it is helpful.
January 29, 2009 at 3:01 pm
The images are good... can I use this in sqlserver 2000?
thank you very much by your help
AMTZVA
January 29, 2009 at 4:06 pm
Amtzva
This 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.
October 21, 2009 at 12:56 pm
Hi 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"?
Thanks
Sundar
October 21, 2009 at 2:56 pm
I 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...
October 22, 2009 at 2:39 am
SSIS 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.
October 22, 2009 at 12:03 pm
Thanks guys. My databases will be always on different servers. Will the above solution work? What modification I can do?
Thanks
Sundar
October 22, 2009 at 4:29 pm
If 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.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply