Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find and update the Schema changes of a table? Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:05 AM
Points: 138, Visits: 196
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...
Post #1477983
Posted Friday, July 26, 2013 7:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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.
Post #1478004
Posted Tuesday, July 30, 2013 5:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:05 AM
Points: 138, Visits: 196
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?
Post #1478927
Posted Tuesday, July 30, 2013 6:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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
Post #1478963
Posted Wednesday, July 31, 2013 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:05 AM
Points: 138, Visits: 196

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.
Post #1479449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse