SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS dynamic mapping of columns


SSIS dynamic mapping of columns

Author
Message
pandiyan70
pandiyan70
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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
brainy
brainy
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 649
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...
naveed.khawaja
naveed.khawaja
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 150
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:BigGrinestinationTable] + " 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:BigGrinestinationTable] + " SELECT * FROM " + @[User::SourceTable]

Give me a shout if you need any more help.
pandiyan70
pandiyan70
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 13
Thanks guys. My databases will be always on different servers. Will the above solution work? What modification I can do?

Thanks
Sundar
naveed.khawaja
naveed.khawaja
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 150
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.
vithasun
vithasun
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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.
brainy
brainy
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 649
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.
VickyWinner
VickyWinner
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 171
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.
Kiran Chennupati-446386
Kiran Chennupati-446386
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
naveed.khawaja
naveed.khawaja
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 150
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search