Data source file name has a timestamp

I have 2 years worth of data that are stored in individual .dbf files for each day. Is there a way to 1 quickly import all of these tables into one and 2. move the timestamp from the file name to a date column?

Any help would be greatly appreciated

The For Each Loop has a file enumertor. You end up with the filename in a variable. You can then use this variable in two ways -

1 Use it in an expression for a connection, so that the connection string is changed to pick up the new file. You would use this connection for your source component, in a Data Flow within the loop itself.

2 Add a derived column transform in your data flow, and use the variable again, grab the date part and pass out in a new column.

Data from three Tables (Joins)

Thanks for your responses.
Well the time can be same and cannot be same
For ex:
Table1
(timestamp,readingA)
(2/2/2005 12:09:26,123)
(2/2/2005 12:19:26,324)
Table2
(timestamp,readingB)
(2/2/2005 12:09:26,321)
(2/2/2005 12:29:26,657)
Table3
(timestamp,readingC)
(2/2/2005 12:09:26,231)
(2/2/2005 12:39:26,987)
Result Desired
(timestamp,readingA,readingB,readingC)
2/2/2005 12:09:26,123,321,231
2/2/2005 12:19:26,324,,
2/2/2005 12:29:26,,657,
2/2/2005 12:39:26,,,987
AS you can see the timestamp which is common in all tables is
displayed once alongwith its readings, but the one which is not common
is displayed alongwith its readings while other readings are blank.
I hope now it is clearThis is a typical xtab problem. Here is a trick.
select timestamp,max(case when tb=1 then reading else 0 end) readingA,
max(case when tb=2 then reading else 0 end) readingB,
max(case when tb=3 then reading else 0 end) readingC
from(
select *,1
from tb1
union all select *,2
from tb2
union all select *,3
from tb3) derived(timestamp,reading,tb)
group by timestamp
Do check out these:
http://support.microsoft.com/kb/175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/
-oj
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502022352.7cddb731@.posting.google.com...
> Thanks for your responses.
> Well the time can be same and cannot be same
> For ex:
> Table1
> (timestamp,readingA)
> (2/2/2005 12:09:26,123)
> (2/2/2005 12:19:26,324)
> Table2
> (timestamp,readingB)
> (2/2/2005 12:09:26,321)
> (2/2/2005 12:29:26,657)
> Table3
> (timestamp,readingC)
> (2/2/2005 12:09:26,231)
> (2/2/2005 12:39:26,987)
> Result Desired
> (timestamp,readingA,readingB,readingC)
> 2/2/2005 12:09:26,123,321,231
> 2/2/2005 12:19:26,324,,
> 2/2/2005 12:29:26,,657,
> 2/2/2005 12:39:26,,,987
> AS you can see the timestamp which is common in all tables is
> displayed once alongwith its readings, but the one which is not common
> is displayed alongwith its readings while other readings are blank.
> I hope now it is clear|||On 2 Feb 2005 23:52:26 -0800, Pradeep wrote:

>Thanks for your responses.
>Well the time can be same and cannot be same
>For ex:
>Table1
>(timestamp,readingA)
>(2/2/2005 12:09:26,123)
>(2/2/2005 12:19:26,324)
>Table2
>(timestamp,readingB)
>(2/2/2005 12:09:26,321)
>(2/2/2005 12:29:26,657)
>Table3
>(timestamp,readingC)
>(2/2/2005 12:09:26,231)
>(2/2/2005 12:39:26,987)
>Result Desired
>(timestamp,readingA,readingB,readingC)
>2/2/2005 12:09:26,123,321,231
>2/2/2005 12:19:26,324,,
>2/2/2005 12:29:26,,657,
>2/2/2005 12:39:26,,,987
>AS you can see the timestamp which is common in all tables is
>displayed once alongwith its readings, but the one which is not common
>is displayed alongwith its readings while other readings are blank.
Hi Pradeep,
Try if this accomplishes what you want:
SELECT COALESCE (A."timestamp", B."timestamp", C."timestamp"),
A.readingA, B.readingB, C.readingC
FROM Table1 AS A
FULL OUTER JOIN Table2 AS B
ON B."timestamp" = A."timestamp"
FULL OUTER JOIN Table3 AS C
ON C."timestamp" = COALESCE (A."timestamp", B."timestamp")
(untested)
By the way, I suggest you use another name for the timestamp column, as
timestamp is a reserved word in SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)