July 24, 2012 at 1:06 am
Hi,
I am using a data flow task to import a excel sheet into Sql db.
We have many databases , and I want to make a single connection to SQL SERVER to any one of the databases (say master) and then connect to any of the other database. I am storing all the connection details into config file.
Now when I use the ole db destination task , it shows me the tables only under master , I remember in DTS it used to show all the tables with database name like tempdb.dbo.tablename , master.dbo.tablename.
Can we achieve the above in SSIS , i.e make a connection to sql server and then choose the different database under ole db destionation task.
July 24, 2012 at 1:18 am
I know we can do it via SQL Command option , but is there a way where in all tables of all the database can be shown under the dropdown Table/View.
July 24, 2012 at 1:20 am
You can directly type the destination table in the OpenRowset property. You can try with myDatabase.mySchema.myTable, but I can't garantuee it will work.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 24, 2012 at 1:32 am
Hi,
In desination db Connection Manager, you can use the "expression" properties to set the db name dynamically. (Assign the DB name value to a Variable). Use this variable in your Config file.
Every time you change the value of the variable, it will look for that DB and will insert the data to the mentioned table(You have to select the destination table during your design and column mapping part).
Please let me know, if you have queries.
Regards,
Karthik.
Regards,
Karthik.
SQL Developer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply