Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find and update the Schema changes of a table?


Find and update the Schema changes of a table?

Author
Message
prakashr.r7
prakashr.r7
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 197
Hi Friends,

I have around 25 tables where i am frequently using to put the data from live server to my local . The schema's of the live database gets changed frequently. I mean we add one or more columns evertime. When i use my SSIS package , to pull the data from live server to my local, it is messing up. I have to drop the database and recreate it with new schemas and update the package as well. So what i am looking for is

Just Curious..
1. How do we create a script to update the schema ? I know there is Red Gate tool which does this comparision of the schemas. But is there a way to create some SQL statements to update the schema? (because I can't buy the red gate software. price is not affordable :-))
2. How can we avoid the SSIS package getting this error? You know if we have added some fields in the table, then i have to update the package as well....and then using it.

Any suggestions would be really appreciated. Sorry in case , if my questions are meaning less...
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4510 Visits: 9479
I wouldn't even attempt to use SSIS to make daily copies of tables with schemas that can change daily.

First, you can query INFORMATION_SCHEMA.COLUMNS to determine if there have been any changes made to the column definition of a source table. If no changes, then just insert into target table from source table. If there have been schema changes in source table, then drop the target table, and SELECT INTO target table from source table.

For example:

drop table [TargetTable];
select * into [TargetTable] from [SourceTable];



As simple as that, you can create a new target table with schema and data.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
prakashr.r7
prakashr.r7
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 197
Eric M Russell (7/26/2013)
I wouldn't even attempt to use SSIS to make daily copies of tables with schemas that can change daily.

First, you can query INFORMATION_SCHEMA.COLUMNS to determine if there have been any changes made to the column definition of a source table. If no changes, then just insert into target table from source table. If there have been schema changes in source table, then drop the target table, and SELECT INTO target table from source table.

For example:

drop table [TargetTable];
select * into [TargetTable] from [SourceTable];



As simple as that, you can create a new target table with schema and data.


Okay Eric...How do we update the SSIS packages? You know, once I am done with pulling the data into local....i need to do some conversion, so when i use my SSIS...it's gettting error..So every time i have to open the package through BIDS and update it with latest schemas....Is there any way to avoid opening everytime?
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4510 Visits: 9479
prakashr.r7 (7/30/2013)
Eric M Russell (7/26/2013)
I wouldn't even attempt to use SSIS to make daily copies of tables with schemas that can change daily.

First, you can query INFORMATION_SCHEMA.COLUMNS to determine if there have been any changes made to the column definition of a source table. If no changes, then just insert into target table from source table. If there have been schema changes in source table, then drop the target table, and SELECT INTO target table from source table.

For example:

drop table [TargetTable];
select * into [TargetTable] from [SourceTable];



As simple as that, you can create a new target table with schema and data.


Okay Eric...How do we update the SSIS packages? You know, once I am done with pulling the data into local....i need to do some conversion, so when i use my SSIS...it's gettting error..So every time i have to open the package through BIDS and update it with latest schemas....Is there any way to avoid opening everytime?

My opinion is that this table copy process should not be performed in SSIS, because the schemas often change. It should be done in pure T-SQL and perhaps scheduled as a job using SQL Agent, if it needs to be automated.

If this table copy requirement is part of a larger process workflow that must run in SSIS, then you could also add the INSERT INTO or SELECT INTO statements inside a T-SQL task. If you need to do something with these tables in SSIS, and keep getting errors related to changes in table schema, then there is the 'Delay Validation' property, which can be set True, or you can temporarily set your package to Work Offline mode.
http://msdn.microsoft.com/en-us/library/ms137625.aspx


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
prakashr.r7
prakashr.r7
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 197

If this table copy requirement is part of a larger process workflow that must run in SSIS, then you could also add the INSERT INTO or SELECT INTO statements inside a T-SQL task. If you need to do something with these tables in SSIS, and keep getting errors related to changes in table schema, then there is the 'Delay Validation' property, which can be set True, or you can temporarily set your package to Work Offline mode.
http://msdn.microsoft.com/en-us/library/ms137625.aspx


Thank you , Eric....Thanks for the information that you shared.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search