Hi Everyone

I'm new to SQL and SSIS, and I have a problem. I have a set of tables which I need to copy from a database on one server to a database on a different server for reporting purposes.
The problem I have is that when copying a table with SSIS, you either need the destination table to exist already, or you need to create a new table with a "Create Table" script. This causes me problems as from time to time, new fields are added to the source table, and the way SSIS seems to work, this change will not be reflected in the destination table.
How can I get around this? A collegue suggested dynamic SQL in the create table script, but I'm sure there must be a much simpler way?

Thank you in advance.

I am always at odds with using SSIS although I use it a lot. Due to the fact that the source data may change then I would take the suggestion from your colleague and you may have to write a dynamic sql loader.

Questions come to mind. Do you need to load all of the source data? The columns that change or are not there, what should happen with missing data when it is imported? Does it need to be set to any value or can it be left as NULL?

I wrote a data loader that took tax information from the IRS as an Excel file and loaded a database. I used an SSIS package to load the Excel file into a staging table. Then I called a loader to take the data from the staging table to the production table. This load process ran from the command prompt using DTEXEC (to run the SSIS package) & OSQL (to run the stored procedure to transform the data and load the production table). I was able to load 1.4 million rows in about 10 minutes. I wasn't concerned about performance as much as getting the data loaded. This could have been done all within the SSIS package but after 1 day of fumbling around how to get it to work I just made it real simple... KISS solutions works best!

My suggestion to you is to port the data into a staging table then have a stored procedure that ports the data over to a production table. If you want to get fancy you can actually write a trigger and hang it off the staging table and when it fires it loads the data right to the production table. It may be slower using a trigger but it will work. It also depends on the volume of data.

Hope this helps.
Kurt
Sr. DBA
Tarrytown, NY