Checking Record Consistency Between Tables Using SSIS

Feb 1, 2008

Hello,
I have an SSIS package that affects data in fourteen tables. This SSIS packge is supposed to insert, update, delete and apply various business rules to the data in the new database tabes in the same way that it occurs in the DTS package that affects the old tables in the former database.

After both the DTS packge and SSIS packages are run, the data in the fourteen tables from both databases need to mirror each other. I figure this is the best way that I can know that the new SSIS package is working correctly.

Is there something I can do in an SSIS package that can compare all of the tables between the two databases, record for record then (maybe as error output) produce output in the case where a mistmatch was detected between two given tables? Would a lookup transformation be helpful for this kind or thing, or should I think about doing it some other way?

The old database is on SQL Server 2000, and the new one on SQL Server 2005 64 bit. I've run into problems creating a linked server between the two to where I could just run queries to do this kind of check.

Any ideas?

Thank you for your help!

cdun2

View 3 Replies

Checking For A New Record

Jan 5, 2005

Good morning everyone I am writing a windows forms application that will work similar to the windows messenger popup. I need this application to display a message to the user whenever a record is added to table in my DB. Any help would be appreciated in explaining how I can check the DB for new records.

View 3 Replies View Related

Checking If No Record Is Returned

Aug 1, 2004

hi,

I basically want the .net version of rs.eof
does anyone know what the code would be using the execute scalar method?

Thanks

View 2 Replies View Related

Need Help Checking To See If More Than 1 Record Updated.

May 21, 2006

I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message.

My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far.

I am new with Triggers, so any help or advice is appreciated.

Code:

CREATE TRIGGER trgPaperCheck
ON BOOK
AFTER UPDATE
AS
IF UPDATE(Paperback)
BEGIN
DECLARE @count AS INT
SELECT @count = COUNT(Paperback)
FROM Book
IF @count > 1
BEGIN
PRINT('Only one Book record can be updated at a time!')
ROLLBACK TRANSACTION
END
ELSE IF @count =1
BEGIN
PRINT('Paperback Updated')
COMMIT TRANSACTION
END
END

View 2 Replies View Related

Checking To See If A Record Exists Before Inserting

Feb 14, 2007

I can't seem to get this work. I'm using SQL2005
I want to check if a record exists before entering it. I just can't figure out how to check it before hand.
Thanks in advance. Protected Sub BTNCreateProdIDandName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNCreateProdIDandName.Click
' Define data objects
Dim conn As SqlConnection
Dim comm As SqlCommand

' Reads the connection string from Web.config
Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
' Initialize connection
conn = New SqlConnection(connectionString)

' Check to see if the record exists
If
comm = New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @BuilderID) AND (OptionNO = @OptionNO)", conn)

Then
'if the record is exists display this message.
LBerror.Text = "This item already exists in your Option List."
Else

'If the record does not exist, add it. FYI - This part works fine by itself.
comm = New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@BuilderID, @OptionNO, @OptionName)", conn)

comm.Parameters.Add("@BuilderID", System.Data.SqlDbType.Int)
comm.Parameters("@BuilderID").Value = LBBuilderID.Text

comm.Parameters.Add("@OptionNO", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionNO").Value = DDLProdID.SelectedItem.Value

comm.Parameters.Add("@OptionName", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionName").Value = DDLProdname.SelectedItem.Value

LBerror.Text = DDLProdname.SelectedItem.Value & " was added to your Option List."

Try
'open connection
conn.Open()
'execute
comm.ExecuteNonQuery()
Catch
'Display error message
LBerror.Text = "There was an error adding this Option. Please try again."
Finally
'close connection
conn.Close()
End Try

End If

End Sub

View 8 Replies View Related

Problem In Checking Null Record

Apr 30, 2007

Hello, I am using sql server 2000 as my backend.If there is any null record in my table , I am getting following error:*********** Conversion from type 'DBNull' to type 'String' is not valid. ************** to overcome this error I want to check if its null then don't do anything else do something.I have tried like this:If rs.fields("ph").value="NULL" thenelse.................end if But this does not solve my problem.Please help me i anyone can.Thanks.

View 1 Replies View Related

Checking A Table To See If A Record Already Exists

Sep 19, 2007

I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.

I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.

I want to write a query that will check each row in the third table to see if
a) the employee exists in the Employees table
b) the company exists in the Companies table and
c) the employee is listed under the correct company

The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.

Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.

Anybody got any ideas?

View 4 Replies View Related

Checking Whether Record ID Exists In Another Query

Jan 10, 2014

I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.

There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:

Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]

From gridhdr pg inner join customers c on c.grid_id = pg.grid_id

Order by pg.grid_id

View 4 Replies View Related

Checking To See If A Record Exists Else Insert

Apr 30, 2008

I am checking to see if the source record is available in the target table using a lookup transformation and if not found i have to insert this record.
I have connected the error flow of the lookup transformation to the target. I am acheiving expected results, but is this the best practise? I have not connected to the green arrow to any task.

View 6 Replies View Related

Checking To See If A Record Exists And If So Update Else Insert

Feb 9, 2007

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

Phil

View 60 Replies View Related

Checking Value In 2 Tables.

Aug 3, 2007

i want to check a value in 2 tables. the 1st table i want to check if the value exists and from the 2ed table i samething.
i came up with this but when it's doing the second if i get error on the page.
Dim ReturnVal As Integer
Dim ReturnVal2 As IntegerDim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)", conn)
'To check if the # is registered.Dim cmd2 As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [t_RegisterInfoTemp] WHERE ([ReportNumber] = @ReportNumber)", conn)
cmd.Parameters.AddWithValue("@ReportNumber", txtReport.Text)cmd2.Parameters.AddWithValue("@ReportNumber", txtReport.Text)
conn.Open()
ReturnVal = Convert.ToInt32(cmd.ExecuteScalar())
conn.Close()
'This checks if the # is correct.
If ReturnVal > 0 Then
'This checks if its registered with someone else.
conn.Open()
ReturnVal2 = Convert.ToInt32(cmd2.ExecuteScalar())
conn.Close()
If ReturnVal2 <> 0 ThenServer.Transfer("regccinfo.aspx")
Else
lblError.Text = "the # is registered with someone else."
End If
Else
lblError.Text = "the # does not exists."
txtReport.Focus()
End If

View 6 Replies View Related

Checking For Modified Dates For Tables

Jul 11, 2003

In the olden days when I used databases that kept each table in a separate file, I used to be able to tell when a specific table was modified by the date stamp on the file.

Is there a method in SQL server to check the "last modifed date" at the table level?

I didn't see anything in sysobjects.

View 5 Replies View Related

Checking Duplicate Values Two Tables

Feb 5, 2005

Hi All,

Is it possible in SQL to Restrict value in one table checking a value on anather tables.

Scenerio-1.

I have two table let say,
Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.

Scenerio -2.[/B]

I have table called [B]SIC.

This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.

Please Advise.
Vijay

View 1 Replies View Related

Checking All Tables That Have Specific Column Name?

Jan 5, 2012

Is there a command I can use to tell me all the tables in my schema that have a column name "Id"?

View 5 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 6 Replies View Related

Stored Procedure Checking 2 Tables

May 12, 2004

ok, ill lay it out like this, and try not to be too confusing.

I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,

i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item

so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.

well i tried not to be confusing, but i think i failed

View 1 Replies View Related

Sep 28, 2007

Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!

View 6 Replies View Related

Trying To Identify A Unique Record On Two Separate Tables

Oct 31, 2007

Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index.
Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P.

View 8 Replies View Related