November 7, 2005 at 5:45 am
I have a DTS package which is importing data from an access 97 database to an sql server 2000 one. I have 2 identical tables one in the access 97 database one on the sql server. The access 97 database gets updated with new imformation weekly, I then want to update the table in sql server to contain the new data. The problem arises as I am trying to import the data using a data transform task and want to import all the data after the maximum date in the sql server table. How do I use the maximum date from the sql server table on the access data base. I thought I could maybe use a lookup? or a global variable? but am not sure how.
basically i want to import all the rows from the access 97 table to the sql server table which have a date greater than the maximum date in the sql server table.
November 8, 2005 at 4:03 am
Look into the data driven query task:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemtsk1_9w2z.asp
November 8, 2005 at 5:08 am
Hi,
I had a similar requirement for incrementally loading data into our DW and this is how I did it.
To do this you will need a global variable and a dynamic properties task.
1. Create the global variable and a dynamic properties task.
2. In the dynamic properties choose your global variable and then the source of the global variable as SQL.
3. Choose your SQL server connection in the dynamic properties window.
4. Write the SQL statement to find the largest date. (ie. Select Max(date) From table1)
5. Use a SQL statement as the source for you datapump task add the date criteria as aparameter like this. (Select * From table Where date > ?)
6. Click the parameters button and select the parameter from the list.
Hopefully this should work!
Daniel
November 8, 2005 at 6:33 am
Hi,
I used a similar approch as above:
1. Create a Execute SQL Task connection SQL server and a global variable like Maxdate.
2 SQL looks like select max(date) from etc
3 Execute this first
4 In the datapump transformation the script looks like:
' Copy each source column to the destination column
Function Main()
if DTSSource("Date") > DTSGlobalVariables("Maxdate").value then
DTSDestination("Value") = DTSSource("Value")
DTSDestination("Date") = DTSSource("Date")
'etc
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
end function
Maybe not the "best" solution but it works.
November 9, 2005 at 4:34 am
Excellent thank you, all sorted now. Knew I needed to use a global variable but wasnt quite sure how, cheers
November 21, 2005 at 4:07 am
Ok the problem I have now is that I have the date I want as my global variable, but the query that imports from access at some point sees it as american format so I get the wrong data imported. When I look at my global variable and my access data they are both in English format.
I thought surrounding the date with #'s might but I dont seem to be able to do this.
Any suggestions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply