Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSIS dynamic mapping of columns Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 12:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, October 21, 2009 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 10:25 PM
Points: 113, Visits: 638
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
Posted Thursday, October 22, 2009 2:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 22, 2009 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 22, 2009 4:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Saturday, February 20, 2010 4:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Sunday, February 21, 2010 3:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 10:25 PM
Points: 113, Visits: 638
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
Posted Monday, June 21, 2010 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 1:00 PM
Points: 2, Visits: 151
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
Posted Thursday, July 08, 2010 11:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, July 08, 2010 4:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase ««123»»

Permissions Expand / Collapse