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 buseness 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

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 intermediatory solution (I was thinking of RAW files) and then a second step that reads from the intermediatory. 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.

P.

• Edited by pkssandhu 16 hours 51 minutes ago

In all seriousness, then why use SSIS at all?  It makes no sense. 

SSIS cannot handle your requirements.  You will need to change your requirements or be more flexible.

SSIS cannot ever "be metadata changes independent".  If the source or destination table changes, SSIS packages will generally break or at least give a warning.

The simplest way to do what you are trying to accomplish would be to use replication.  But you say that is not an option.

The other way would be to use BCP and output all the data to flat files, then BCP them into the 2nd server.  This would require you to truncate and reload all the data, every time.

I have three news for you:

Bad News:

• SSIS is not primitively designed to work with dynamic metadata, unless you are pretty strong in .NET and can design your package programatically; which isn't difficult looking at your requirement. :) I can say because I have done it before.

Good News:

• You can do this by building your dynamic SQL in script task. Then use execute SQL to get the data and insert into some other table. And then, data flow task to get the data and insert into your server table (presuming that your server data table has the same structure.. if not than alter destination table based on the dynamic SQL you have generated in script task)

Neutral News:

• Linked server, OPENQUERY and OPENROWSET aren't that bad ! Especially when you have weird requirement.

Hope this will help

@Bendare2: True, but my directive is to stick with SSIS so I am trying to do that :)

@Tom Philips: Thank you but as you have already mentioned it would require me to load the whole table every time which will increase my load times and I cannot filter data

@Anuj: Thank you for your input. I don't have issues in coding the package in .Net. As I mentioned before in my reply to Bendare2, I am trying to stick with SSIS if I can with the given constraints.

Coming to the GOOD NEWS part of your post, are you saying that I can execute the query and load the result set into a table on any server because I can define connection strings and login credentials programmatically in script task? 

I am glad it helped a bit !

I will try to explain my solution in detail: (+1 more solution) :)

• Get source table metadata (may be from Information schema)
• Build your SELECT clause in script task, [and also based on source table column information you can alter your destination table also.] 
• Now, till above step you are ensure that your source and destination table have same structure. Then you can use Execute SQL Task like

       INSERT INTO YourDestinationTable SELECT Col1,Col2... FROM LinkedServer.DestinationTable

     or

• if you are unsure about data type changes then mark destination as varchar(some valid length) and just dump data from source to destination by converting each column of your source to varchar. [In SQL Source, SELECT CAST(Col1 AS Varchar(100)), .... or CONVERT
• Now, because your both table have same data type in all case then you can use data flow task to transfer rows from source to destination.
• Once you are done with the data transfer, alter destination table data type as per source metadata. [SQL will do it if it don't violets any rule]

Hope this will help

@Anuj: Right and again if I understood you correctly, you are depending on linked servers here. I cannot use linked servers. Not because I personally do not like to use them but because I am not allowed too. However, that did give me another idea. I am going to go try that and will report back.

Thanks

Yeah, I can understand that. But my second solution doesn't use linked server. 

3. We cannot use linked servers (not a good idea anyway)  

  use cache server or staging server.it will speed up ptocess

How about this

1--Create Flat files dynamically with | or comma delimited.  You can make selection to choose the tables you want by using below link. It will automatically read the columns , so if tomorrow you drop or add new column to source tables, the code will be able to handle and generate new files with modified metadata.

How to create flat files for all the tables from a SQL Server Database

2--As your source can change, so your flat file created can come with new columns or less columns, so why not to drop the table each time and generate new on destination and load the files to them according to the names of files ( table names in other words)

use the below link to create table and load files by name. This can be modified if you don't like to drop the table on destination and make changes manually and just load the data

How to create tables for multiple pipe delimited files and load them dynamically to those tables in SSIS Package

or tables will be handles manually, you can use this link

How To Load Files in Different Tables According To The Name Of File

3--Now you have the data on destination and you can run your sql statement on it, you can have SQL Statement files in a folder or you can have them in a table and just run them , you can use below links as starting point

Execute .SQL Files from Folder in SSIS Package

Run SQL Statements from Table

Execute SQL Statements From a SQL Server Table in SSIS Package

All the best!

Thank you all for your input. Anju's post gave me an idea and I think I now have what I need. 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/credentials from DTS variables
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.

Sometimes it just helps to talk out loud.

Thank you.

P.

• Marked as answer by pkssandhu 22 minutes ago

Thank you Aamir. I haven't tried your solution as I already had what I was looking for but I have given it a read and looks like it should work so I am marking it as an answer.

Cheers!