December 7, 2006 at 10:00 am
Hi,
I need to archive weekly data from AA server archive database to BB server archive database. I need to do a rowcount on AA server archive database of that week and insert into BB server archive database. Before the insert I want to compare the rowcount between these 2 servers and if is equal I want to commit the insert in BB server archive database and delete it on AA server archive database of that week. The DTS will create on BB server and currently I do not have remote linked server to AA server just SQL server connection. What is the best way to do it. Please advise. Thanks.
December 8, 2006 at 9:14 am
Although I do not have the confidence that this is the best solution, this is what I would do: Archiving weekly data by means of a datapump. One SQL task per server for rowcounts, in each task storing the result in a global variable through an output parameter. Use a SQL task to insert a rowcount into a table using a global variable as an input parameter. Compare the rowcounts in an Activex script and generate the insert SQL and store it in a global variable. In a Dynamic Properties task, update the SourceSQLStatement of the insert SQL task with the contents of the global variable thus created. Commit and delete via SQL tasks.
I hope someone gives you a better solution, but this is what I would do.
December 8, 2006 at 12:14 pm
Hi Carlos,
I believe you are pointing me to the right direction. Is it possible for you to simply sketch out the DTS how it flows for example below.
SQLtask AA = count rows on AA server and output parameter in global variables
SQLtask BB = count rows on BB server and output parameter in global variables
SQLTaskInsertRowcount = insert rowcount into a table for input parameter --This one I don't understand, do you ,man create a temp table and insert both row counts for AA and BB servers in the SQL task?
Active X = where does it reside in the flow?
SQLtask AA ------> SQLTask BB ------->SQLTaskInsertRowcount
Thanks.
December 8, 2006 at 3:30 pm
SQLtask AA ----> SQLTask BB -----> ActivexTask -----> DynamicProperties Task -----> SQLTask AADelete -----> SQLTask BBInsert
The activex task assumes that the previous two tasks have populated the global variables AACount and BBCount.
The structure is approximately as follows:
Function Main()
dim sAACount, sBBCount, sAASQL, sBBSQL
sAACount = DTSGlobalVariables("AACount")
sBBCount = DTSGlobalVariables("BBCount")
IF sAACount = sBBCount THEN
sBBSQL = 'insert into ServerBBTable values (' + sAACount + ')'
sAASQL = 'delete from ServerAATable where SomeCriteria = CriteriaValue'
ELSE
sBBSQL = ''
sAASQL = ''
END IF
DTSGlobalVariables("AASQL") = sAASQL
DTSGlobalVariables("BBSQL") = sBBSQL
Main = DTSTaskExecResult_Success
End Function
The DynamicProperties task populates:
the sql Statement in the SQLTask AADelete with global variable AASQL
the sql Statement in the SQLTask BBSQLTaskInsert with global variable BBSQL
December 19, 2006 at 8:55 am
Hi Carlos,
I am not able to create the SQLTask AADelete global variable. it stated that ;The sql statement does not contain any parameters" when I click on the parameter buttons. Below is my syntax. Please advise. Thanks.
delete from dbo.Test
where date >= (getdate () -15) and date <= (getdate () -1)
December 19, 2006 at 9:34 am
You do not need a global variable in the SQL task AADelete. The entire SQL statement for that task was formed in the Activex task and inserted into its proper location by the Dynamic Properties task. When you are coding the SQL task AADelete you can just put
select * from dbo.test
You can do this because that SQL statement is replaced when the Dynamic Properties task executes. On the 12/8/2006 3:30:00 PM post, I attempted to describe the Activex task that forms the SQL statement. In case you haven't used the Dynamic Properties Task before, please do the following:
· Drop a Dynamic Properties Task icon onto the DTS designer
· Click on the Add button
· Open the Tasks by clicking on the + sign next to Tasks in the left pane
· Select the SQL task whose SQL statement you want to replace
· When the Package Properties dialog display, select the SQL Statement and click on the Set button
· When the Add/Edit assignment dialog displays, select Global Variables from the Source dropdown, then select the global variable from the Variable dropdown (this is the GV into which you placed the formed SQL statement back in the Activex task)
· Click OK, and OK
I hope this helps. It is a little convoluted but it works fine.
December 26, 2006 at 3:04 pm
Hi Carlos,
Is this statement below valid in the ActiveX? Because it returns an error?
sBBSQL = 'insert into ServerBBTable values (' + sAACount + ')'
especially with the (' + sAACount + ') what does this do? Also my original statment that I wanted to insert is as below for sBBSQL and how can I fit into the Active X. Please advise. Thanks.
insert into BBTest(A,B,C,D,E, Date)
select A,B,C,D,E,Date
from AA where Date <= getdate() -7
December 26, 2006 at 4:36 pm
Don't forget that when you're working in activex, you have to use double quotes. In your example you're using single quotes.
Also remember that when you're building SQL statements in Activex, you have to use single quotes for the actual SQL when you're using character columns (varchar or char), but double quotes for the Activex commands. Of course, you don't have to use single quotes when working with integer, numeric, etc. It's a little tedious but after a while it will be second nature. Hope this helps. In the example I sent you originally, I think I put one sql statement per line, but FYI, it doesn't matter. Just to make it work, as long as you leave a space between statements, it'll work. You can clean it up later.
December 27, 2006 at 8:05 am
Do I have create open connections for these 2 servers AA and BB in Active X? Please advise. Thanks.
December 27, 2006 at 8:28 am
Not at all; remember the activex task is only forming the SQL and placing it in Global Variables (refer to the Activex sample from the 12/8/2006 3:30 PM post). It does not access the database at all. The next task, the dynamic properties task(refer to the first line of the same post), takes those global variables and uses their contents to replace the SQL in the SQL tasks. You do not refer to the database until the SQL task.
December 27, 2006 at 9:19 am
Hi Carlos,
In my ActiveX sBBSQL below --->error is where it failed. Can you tell what went wrong? I have tried both single and double quotes but to no prevail. Thanks.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim sAACount, sBBCount, sAASQL, sBBSQL
sAACount = DTSGlobalVariables("AACount")
sBBCount = DTSGlobalVariables("BBCount")
IF sAACount = sBBCount THEN
---> error failed in sBBSQL below How does ActiveX know where to retrieve data from AATest table in AA server in this SQL statement without a connection?
sBBSQL = 'insert into BBTest(A,B,C,D,E, Date)
select A,B,C,D,E,Date from AATest where Date >= getdate() -7'
sAASQL = 'delete from AATest where date >= (getdate() -7) '
ELSE
sBBSQL = ''
sAASQL = ''
END IF
DTSGlobalVariables("AASQL") = sAASQL
DTSGlobalVariables("BBSQL") = sBBSQL
Main = DTSTaskExecResult_Success
End Function
December 27, 2006 at 10:01 am
Hi Carlos,
In my ActiveX sBBSQL below --->error is where it failed. Can you tell what went wrong? I have tried both single and double quotes but to no prevail. Thanks.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim sAACount, sBBCount, sAASQL, sBBSQL
sAACount = DTSGlobalVariables("AACount")
sBBCount = DTSGlobalVariables("BBCount")
IF sAACount = sBBCount THEN
---> error failed in sBBSQL below How does ActiveX know where to retrieve data from AATest table in AA server in this SQL statement without a connection?
sBBSQL = 'insert into BBTest(A,B,C,D,E, Date)
select A,B,C,D,E,Date from AATest where Date >= getdate() -7'
sAASQL = 'delete from AATest where date >= (getdate() -7) '
ELSE
sBBSQL = ''
sAASQL = ''
END IF
DTSGlobalVariables("AASQL") = sAASQL
DTSGlobalVariables("BBSQL") = sBBSQL
Main = DTSTaskExecResult_Success
End Function
December 27, 2006 at 11:07 am
Sorry, I got the first notification but failed to see that it was on the second page.
Remember that you are forming SQL not executing it. After you form it, you store it in global variables where it will be picked up by the subsequent step. The correct code follows:
Function Main()
dim sAACount, sBBCount, sAASQL, sBBSQL
sAACount = DTSGlobalVariables("AACount")
sBBCount = DTSGlobalVariables("BBCount")
IF sAACount = sBBCount THEN
sBBSQL = "insert into BBTest(A,B,C,D,E, Date) "
sBBSQL = sBBSQL + "select A,B,C,D,E,Date from AATest where Date >= getdate() - 7"
sAASQL = "delete from AATest where date >= (getdate() - 7) "
ELSE
sBBSQL = ""
sAASQL = ""
END IF
DTSGlobalVariables("AASQL") = sAASQL
DTSGlobalVariables("BBSQL") = sBBSQL
Main = DTSTaskExecResult_Success
End Function
December 27, 2006 at 11:17 am
Hi Carlos,
I want to thank you very much for your help. This work for me and also I have made a mistake by putting double single qoutes instead of double quotes. God bless you.
December 27, 2006 at 11:25 am
Glad to help. I have received significant help from this forum and I am happy that I was able to give something back. Thank you for the blessing.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply