SSIS dynamic mapping of columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » SSIS dynamic mapping of columns

26 posts, Page 2 of 3

««1 2 3 »»

SSIS dynamic mapping of columns

Rate Topic

Display Mode

Topic Options

Author

Message

pandiyan70

Posted Wednesday, October 21, 2009 12:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 13, 2011 8:11 AM
Points: 3, Visits: 13

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

Post #806735

brainy

Posted Wednesday, October 21, 2009 2:56 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:08 AM
Points: 113, Visits: 624

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...

Post #806831

naveed.khawaja

Posted Thursday, October 22, 2009 2:39 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 3:59 AM
Points: 20, Visits: 142

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.

Post #806997

pandiyan70

Posted Thursday, October 22, 2009 12:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 13, 2011 8:11 AM
Points: 3, Visits: 13

Thanks guys. My databases will be always on different servers. Will the above solution work? What modification I can do?

Thanks
Sundar

Post #807371

naveed.khawaja

Posted Thursday, October 22, 2009 4:29 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 3:59 AM
Points: 20, Visits: 142

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.

Post #807501

vithasun

Posted Saturday, February 20, 2010 4:42 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 22, 2010 6:27 PM
Points: 16, Visits: 73

hello 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.

Post #869725

brainy

Posted Sunday, February 21, 2010 3:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:08 AM
Points: 113, Visits: 624

Hi Vena

Please 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.

Post #869809

VickyWinner

Posted Monday, June 21, 2010 12:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:01 AM
Points: 2, Visits: 121

Hi

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.

Post #940109

Kiran Chennupati-446386

Posted Thursday, July 08, 2010 11:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 2:13 PM
Points: 1, Visits: 10

Hi Naveed,
Can you send me the example of this code to my email address kiran.nalluri@ingenix.com

Apprecite your help

Thanks!
Kiran.

Post #949478

naveed.khawaja

Posted Thursday, July 08, 2010 4:55 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 3:59 AM
Points: 20, Visits: 142

Hi Kiran

I 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.

Naveed

Post #949658

« Prev Topic | Next Topic »

26 posts, Page 2 of 3

««1 2 3 »»

Permissions