May 18, 2015 at 7:58 am
Greetings All
So I am in a predicament. Hopefully, experts here might be able to solve this for me.
I need to move data for some specific tables from Server1 to Server2. This needs to be done using SSIS but no business logic or rules can live within SSIS and the SSIS package should be metadata changes independent. That is to say that if the source query changes, I should not have to modify anything in SSIS.
While thinking about this, you can safely make the following assumptions:
1. Both servers are SQL Server 2008 or higher.
2. Source server is read only and we cannot make any changes
3. We cannot use linked servers (not a good idea anyway)
4. We cannot use database replication/mirroring/log shipping etc.
5. Some of the tables have records in tens of millions. So performance has to be kept in mind as well.
Some approaches I have been thinking about:
1. Have the sql code in a .sql file and then use the execute sql task. This works if I load the query from the file and run it on the destination server but that would mean using linked server to read data from the source server. But, I cannot use linked server and even if I could, I don't think it is a good idea anyway.
2. Load the select query from a .sql file, run it on the source server, download the the data to an intermediary solution (I was thinking of RAW files) and then a second step that reads from the intermediary . So far this is a bust as the set up requires me to select the columns to be written to the RAW file and I cannot have metadata dependency inside SSIS. The step should pick up all available columns automatically.
The overall idea is that if down the road we need to modify the sql queries, all we have to do is to open the .sql file, make changes to the sql query and you are done. We shouldn't have to touch the SSIS at all. The SSIS is only assisting in iterating through the set of sql files, running them and logging the results.
Thank you in advance for your help.
May 18, 2015 at 8:03 am
sandhuz24 (5/18/2015)
Greetings AllSo I am in a predicament. Hopefully, experts here might be able to solve this for me.
I need to move data for some specific tables from Server1 to Server2. This needs to be done using SSIS but no business logic or rules can live within SSIS and the SSIS package should be metadata changes independent. That is to say that if the source query changes, I should not have to modify anything in SSIS.
While thinking about this, you can safely make the following assumptions:
1. Both servers are SQL Server 2008 or higher.
2. Source server is read only and we cannot make any changes
3. We cannot use linked servers (not a good idea anyway)
4. We cannot use database replication/mirroring/log shipping etc.
5. Some of the tables have records in tens of millions. So performance has to be kept in mind as well.
Some approaches I have been thinking about:
1. Have the sql code in a .sql file and then use the execute sql task. This works if I load the query from the file and run it on the destination server but that would mean using linked server to read data from the source server. But, I cannot use linked server and even if I could, I don't think it is a good idea anyway.
2. Load the select query from a .sql file, run it on the source server, download the the data to an intermediary solution (I was thinking of RAW files) and then a second step that reads from the intermediary . So far this is a bust as the set up requires me to select the columns to be written to the RAW file and I cannot have metadata dependency inside SSIS. The step should pick up all available columns automatically.
The overall idea is that if down the road we need to modify the sql queries, all we have to do is to open the .sql file, make changes to the sql query and you are done. We shouldn't have to touch the SSIS at all. The SSIS is only assisting in iterating through the set of sql files, running them and logging the results.
Thank you in advance for your help.
As you've already determined, SSIS is heavily dependent on meta data. There is no obvious way of achieving what you want using a single SSIS package.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 18, 2015 at 8:22 am
I figured but was hoping for some miracle answer. May be somewhere, someone has found a way to achieve this 🙂
Wishful thinking? Yea, may be.
Thank you for the quick reply though.
Cheers.
May 19, 2015 at 1:44 am
Found a way to achieve this. It is a hack way of doing it but it gets the job done.
For anybody interested on how I am doing this:
1. Created a new script task
2. In the Edit Script I load the source and destination server name and credentials
3. I also load the SQL query from .sql file and destination table name.
After I have all of the above, I am using SQLBulkCopy class of System.Data.SqlClient to move data between the two servers. This is ideal because this step is moving data to a staging area on the second server and I do not need to perform any transformations or apply any heavy business logic.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply