Comparing databases

Hi,
I need to compare 2 databases to check for missing objects, columns, etc.
Comparing objects was pretty easy. Just a pair of sql statements on the
sysobjects table and it worked fine.
Now I need to go a level deeper, by comparing missing & different columns in
tables. Is it possible to get the results from the system tables or do I
have to use DMO?
Thanks,
IvanIvan
Visit at http://www.red-gate.com
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to compare 2 databases to check for missing objects, columns, etc.
> Comparing objects was pretty easy. Just a pair of sql statements on the
> sysobjects table and it worked fine.
> Now I need to go a level deeper, by comparing missing & different columns
> in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
> Thanks,
> Ivan
>|||I know that there are quite a few tools that exist, but I need to develop my
own tool as this will be part of yet another bigger suite of tools.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> Ivan
> Visit at http://www.red-gate.com
>
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
etc.
columns
>|||Well , then I'd use DMO objects library
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop
>my
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||What to use depends on whether you prefer to work at the TSQL level or at th
e API level:
TSQL: For 2000, use syscolumns. For 2005, use sys.columns. Or (either versio
n) use the
information_schema views.
API: For 2000, use DMO. For 2005, use SMO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop m
y
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||
> Now I need to go a level deeper, by comparing missing & different columns
in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
Try this
select name from <DB1>..syscolumns where
id=object_id('<DB1>..<TABLE_NAME>') and name not in
(select name from <DB2>..syscolumns where
id=object_id('<DB2>..<TABLE_NAME>'))
--This will return the additional columns in table in another database.
You can well modify it to meet your specefic requirement.

Compare Dates and fail job if no match

This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it

Scenario.

I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.

To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.

SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry

Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.

How do I get this working - can't seem to get my head around how I can compare the 2 dates ?

Derived column transformation has DateDiff function.

or

Script Component, you can write your own function in vb.net

|||Use the precedence constraints in the control flow. Use Execute SQL tasks to get your dates, and then hook them up to a sequence container which houses your data flow(s). The precedence constraint can perform your date comparison.|||

Hey Phil,

Thanks for that... you just confirmed what I had gradually worked out for myself!!

My solution was.....

I created 2 Execute SQL tasks. One to get the source database date, the other to get the "next date" due for loading into the DW and joined them to the first execute package task. But, between the final Execut SQL task & the package task I created a precedence "expression" constraint as follows

@.[User::MasterPackage_vLoaddate]== @.[User::NextLoadDate]

which were the 2 variables assigned from the Execute SQL tasks. On success, the remaining packages run but on failure the job fails.

It works great, so thanks for confirming that....

|||I appreciate that you tried this on your own and got it to work. I also appreciate that you posted your solution for others to use.

Glad it worked out for you!

Thanks,
Phil

Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what

identifies the data as "new" and how many records are you planning to

process at one time.

If you have a primary key in the source data and you are working with

few records < 50k, then you could perform a lookup on the

destination to see if the primary key exists. If it does not then

you could redirect the row to an insert data flow using the error

output. If it does exist (std output), you could (in the typical

case) direct it to an update data flow.

If you have a primary key and are working with many records, you could

perform a merge join on the source and destination and then use a

conditional split to filter out/redirect the rows where the destination

key is null.

If you have some other identying attributes, then the methodology may

change. You may even want to investigate the Slowly Changing

Dimension transform.

Larry|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie

Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what identifies the data as "new" and how many records are you planning to process at one time.
If you have a primary key in the source data and you are working with few records < 50k, then you could perform a lookup on the destination to see if the primary key exists. If it does not then you could redirect the row to an insert data flow using the error output. If it does exist (std output), you could (in the typical case) direct it to an update data flow.
If you have a primary key and are working with many records, you could perform a merge join on the source and destination and then use a conditional split to filter out/redirect the rows where the destination key is null.
If you have some other identying attributes, then the methodology may change. You may even want to investigate the Slowly Changing Dimension transform.
Larry
|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie

Comma Delimited Text File

Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.you can upload a file to the server...there are tons of articles that describe how you can do this.all you need to do is goooogle..now do you want to save the text file as it on the server and maintain a link in a table or do you want to parse the textfile and insert the records into a table ?|||I want to be able to parse the comma delimited textfile and insert the records into a table. Thanks.|||check out my blog i have an article ( under SQL Server 2000 ) tht has some sample code too..

hth|||

Hi,

Could you be able to point me to where is your blog link or the link to where the relate article for this topic. Really appreciate and thanks in advance.