January 5, 2007 at 2:32 pm
Hi. I have used the DTS wizard to import/export, but am new to creating packages. I'm pretty strong in T-SQL but have never worked with VB. I'm trying to teach myself VB and have a bunch of reference books/web sites. However, I am failing miserably.
Problem: I have 2 copies of a database. One exists on SQL Server, the other on MySQL. This is because of security policies at my company. We cannot have a SQL Server db outside of our firewall, so I am using a MySQL database to fullfill the need. So, that being said, any changes made to the SQL Server database need to be replicated in the MySQL database. I'll need to insert new records as well as update existing records. Additionally, the MySQL database will be used for order entry purposes. These orders will need to be brought into the SQL Server database and deleted from the MySQL database at regular intervals.
I have been successful in bulk inserting from SQL Server to MySQL. My problem comes in when I need to find records that either exist in SQL Server and not in MySQL, or finding the records that have changed. I can't link the MySQL Server, so how do I compare the tables? I assumed I needed to use ActiveX, but I have made a huge mess.
What I've done so far: I created 2 row set global variables. One holds the SQL Server values, the other holds the MySQL values. Then, I set up my ActiveX script. I'll paste it here. Please go easy on me. I'm sure I am WAY off base here, but had to try something. I then added my insert and update queries using parameters. I'll paste those as well.
If anyone can help me, I would really appreciate it.
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("VersionID") = DTSSource("VersionID")
DTSDestination("VersionTypeID") = DTSSource("VersionTypeID")
DTSDestination("DisplayName") = DTSSource("DisplayName")
DTSDestination("Version") = DTSSource("Version")
DTSDestination("PublishedDate") = DTSSource("PublishedDate")
DTSDestination("qmsDisplay") = DTSSource("qmsDisplay")
DTSDestination("IsMidCycle") = DTSSource("IsMidCycle")
DTSDestination("TrackBackVersions") = DTSSource("TrackBackVersions")
DTSDestination("VersionSort") = DTSSource("VersionSort")
Dim sSQL
sSQL = "SELECT VersionID FROM tVersion WHERE VersionID <>' " & _
DTSGlobalVariables("MySQLRS").Value & " ' "
If sSQL <> "" Then
Main = DTSTransformstat_InsertQuery
Else DTSTransformStat_UpdateQuery
End If
End Function
----------Queries-------------------
INSERT
INTO tVersion
(VersionID,,,,,,,,,,,,)
SELECT *
FROM tVersion
WHERE VersionID <> ?
update tVersion
set displayname = displayname, versionsort = versionsort
where versionid = ?
The update statement is only updating 2 fields as a test. I really need to update all fields, but wanted to see if my DDQ was going to work first.
January 8, 2007 at 8:00 am
This was removed by the editor as SPAM
November 30, 2007 at 3:07 pm
Hi Cathy,
Did you ever get this to work? I need to do something similar and wanted to utilize the thoughts in your solution if that is ok.
Thanks.
December 6, 2007 at 2:43 pm
Hi. I wasn't able to get the DTS package to work, but I was able to use openquery to pass data. Ultimately, we ended up not using MySQL so the end result is a little fuzzy for me. I'll look back to see how I resolved the problem and post it here.
Cathy
December 7, 2007 at 10:38 am
Hi. I found an old DTS package that I was going to use. Essentially, I set the DTS package up in this way:
I have a connection to the SQL Server 2000 server. I then add another connection to the MySQL server. I add a transform data task connecting the two connections. I do this for every table that I need to grab data from. One sample Transform Data task script is below:
SELECT
ProdReleaseID,
PartNumber,
ReleaseID,
ProductReleaseName,
ApprovalReq,
LastChanged,
ArchiveDate
FROM
ProductRelease
WHERE
ProdReleaseID NOT IN (SELECT * FROM OPENQUERY([SERVERNAME],
'SELECT ProdReleaseID FROM mProductRelease')
)
Upon completion, I add an email task if the insert fails. If it is successful, the package moves on to the next connection set.
I hope this helps.
Cathy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply