Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ells
Starting Member
25 Posts |
 Posted - 2008-05-21 : 06:11:14
|
A few pointers would be appreciated.I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination) For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table select dim.id from SQLServer1.database08.dim_table union select dim.id from SQLServer1.database07.dim_table unionselect dim.id from SQLServer1.database06.dim_table Now next year the names of the databases on SQLServer1 will be database09,database08,database07!Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?Many thanks,Ellsp.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible. |
|
|
ewilson@ucdavis.edu
Starting Member
1 Post |
Posted - 2008-05-21 : 11:12:52
|
| How about moving to stored procedures and use linked servers? Stored procedures will allow for dynamic building of action strings (variable server names). Linked servers means only the connection information for the link would need to be updated (the linked name would stay the same). SSIS can execute stored procedures.To execute a stored procedure from SSIS use a 'Task' and enter the execute statement on the 'SQLStatementSource' line (remember to create a connection to the database and enter the connection name on the 'Task' 'Connection' line).Radical thought: if using stored procedures you can always call them directly from the job, removing the need for SSIS. |
 |
|
|
ells
Starting Member
25 Posts |
Posted - 2008-05-22 : 03:31:10
|
| Thanks. I had already considered SSIS or SP as a way to go to create a SQL string to execute. One thing really caught my attention. Linked servers. I am having a bit of a play with this as I have been usoing this but I think not cleverly enough.If my linked server database was server1\mydb and the database gets moved to server2\mydb is there a way of keeping the name the same but updating the reference in the sys.servers table? A 2000 system I am working on I have like a lemon been going through the DTS packages and altering the sql reference. Ie the sql task says select * from server1\mydb.mytable and I have individually changed these to be server2\mydb.mytable and executed sp_addlinkedserver and sp_addlinksrvlogin to make the connection to server2\mydb.Was there an easier way? Is there a stored proc to amend the linked server entry?Many Many Thanks.Ells |
|
|
|
|
|
|
First time SSIS Long time DTS