Transfering rows from access database to sql sever database

Question

Transfering rows from access database to sql sever database

sachinrshetty

SSCrazy

Points: 2062
More actions
May 21, 2010 at 4:43 am

#143167

Hi all,
In a dataflow task, i m fetching all the rows from one table in MS access database to a table in sql server 2005 database.
So in between these can i make a filter so that i can fetch only rows which contain time equal to today's date.
Please help me thanks in advance.
Sachin

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Do this at source, if possible, by using a query instead of a table as your data source. Does that answer your question, or were you wondering how to write the query?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

You have two options:

either filter on the data when you select it from the Access database (with a WHERE clause or something like that).

EDIT: Phil Parkin apparently already suggested this while I was typing my reply. This is the preferred option.

Or load all the data in and use a Conditional Split.

Use this expression:

myColumn == dateadd("dd",datediff("dd",0,getdate()),0)

This will be the correct output. The default output is the incorrect output.

Map only the correct output to another component.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sachinrshetty SSCrazy Points: 2062 More actions · Answer 3

Hi, Phil Parkin and da-zero

Thanks for your suggestions but as told by da-zero i can't fetch all the rows then deviate rows containing todays date to another component because that table all over contains some 3 lac rows.

So Phil parkin idea would be better performance wise.

Can u tell me how to go with this.

Which Item i have to use after my oledb source component and how to write query for ms access database.

Thanks u both for helping me to solve my issue.

Sachin

sachinrshetty SSCrazy Points: 2062 More actions · Answer 4

Hi I have used query in my source but how to write query to fetch rows containing todays date only.

I have used below query

SELECT TransactionLog.*

FROM TransactionLog

Sachin

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

sachinrshetty (5/21/2010)
Hi I have used query in my source but how to write query to fetch rows containing todays date only.
I have used below query
SELECT TransactionLog.*
FROM TransactionLog
Sachin

I'm confused. You tell us that you can't select all rows (I can understand that, bad for performance).

But then you ask which item you have to use after the OLE DB Source.

That is the Conditional Split, as I've showed you, but for that you have to select all rows.

What Phil Parkin and I suggested, is that you use a where clause in your select statement.

Thus, starting from what you already have:

SELECT TransactionLog.*

FROM TransactionLog

WHERE TransactionLog.DateColumn = dateadd(dd,datediff(dd,0,getdate()),0)

In my code I assume that the date column from your transaction log doesn't contain time values, just data values.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sachinrshetty SSCrazy Points: 2062 More actions · Answer 6

Thank you, I will try your query and revert back to you another 5 mins

sachinrshetty SSCrazy Points: 2062 More actions · Answer 7

Error in WHERE clause near 'TIME'.

Unable to parse query text.

----------------------------------

This is the error i m getting. If i use where clause it shows error 🙁

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

Can you show us the query that you are using?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sachinrshetty SSCrazy Points: 2062 More actions · Answer 9

Hi i have uploaded attachment.

Its showing error bcoz i have used getdate() function.

sachinrshetty SSCrazy Points: 2062 More actions · Answer 10

Hi please find the attachment

Is it becoz ms access database doesn't support getdate() function or something like that

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

Yeah, I'm very fortunate to have never worked with Access before, so I didn't realize the getdate() function doesn't exist in Access. Apparently Now() is the equivalent (Google is your friend).

Also, I used the column DateColumn as an example.

I'm pretty sure you have to replace that with the correct column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246985 More actions · Answer 12

getdate() is not a valid function in Access.

Try using Date() instead.

By the way, Select * is bad practice. It is better if you select all the fields explicitly by name. Select only those fields that you need to optimise performance.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 13

da-zero (5/21/2010)
Yeah, I'm very fortunate to have never worked with Access before, so I didn't realize the getdate() function doesn't exist in Access. Apparently Now() is the equivalent (Google is your friend).
Also, I used the column DateColumn as an example.
I'm pretty sure you have to replace that with the correct column.

You beat me!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sachinrshetty SSCrazy Points: 2062 More actions · Answer 14

Thank u guys. I will work with your logic and revert back to you 🙂