Help in creating the DTS package

[techipa]

Hi All,
I have to create the DTS package to
1) Read only the csv files (example of name Daily_11-01-06 ) from directory \\ABC\ABC_D\Reports
2) Parse and import (Room, ReadingType, Date, TimeInterval,Measurement) into SQL database on daily basis

Following is the sample of csv file. This file stores the temperature, humidity and RPM of each room after

every 15 minutes.

If you see, the format of this file is not uniform.
"CPPS.PRIMARY.P01.BAPI RM TEMP"
- 'P01' is the roomnumber
- TEMP is temperature (Readingtype)
"CPPS.PRIMARY.P01.LH.RMHUM"
- 'P01' is the roomnumber
- RMHUM id Humidity (Readingtype)
"CPPS.RPM.RM021P1"
- p1 is roomnumber
- RPM (Readingtype)

Can anyone suggest me on how to create the DTS which can capture the above things. I would really appreciate any ideas or help to start this project.

"Point System Name:","CPPS.PRIMARY.P01.BAPI RM TEMP"
"Trend Every:","15 Min"
"Date Range:","10/31/2006 00:00:00 - 10/31/2006 23:59:59"
"Report Timings:","All Hours"
"10/31/2006","00:00:00","70.71"," -N- NONE"
"10/31/2006","00:15:00","70.71"," -N- NONE"
""
" ********************************************************************************"
"Point System Name:","CPPS.PRIMARY.P01.LH.RMHUM"
"Trend Every:","15 Min"
"Date Range:","10/31/2006 00:00:00 - 10/31/2006 23:59:59"
"Report Timings:","All Hours"
"10/31/2006","00:00:00","27.94"," -N- NONE"
"10/31/2006","00:15:01","27.80"," -N- NONE"
""
" ********************************************************************************"
"Point System Name:","CPPS.RPM.RM021P1"
"Trend Every:","15 Min"
"Date Range:","10/31/2006 00:00:00 - 10/31/2006 23:59:59"
"Report Timings:","All Hours"
"10/31/2006","00:00:00"," -0.051"," -N- NONE"
"10/31/2006","00:15:00"," -0.052"," -N- NONE"
""
" ********************************************************************************"
"Point System Name:","CPPS.PRIMARY.P02.BAPI RM TEMP"
"Trend Every:","15 Min"
"Date Range:","10/31/2006 00:00:00 - 10/31/2006 23:59:59"
"Report Timings:","All Hours"
"10/31/2006","00:00:00","70.09"," -N- NONE"
"10/31/2006","00:15:00","70.09"," -N- NONE"
""
" ********************************************************************************"
"Point System Name:","CPPS.PRIMARY.P02.RMHUMIDITY"
"Trend Every:","15 Min"
"Date Range:","10/31/2006 00:00:00 - 10/31/2006 23:59:59"
"Report Timings:","All Hours"
"10/31/2006","00:00:00","26.41"," -N- NONE"
"10/31/2006","00:15:00","26.41"," -N- NONE"
""
" ********************************************************************************"
"Point System Name:","CPPS.RPM.RM021P2"
"Trend Every:","15 Min"
"Date Range:","10/31/2006 00:00:00 - 10/31/2006 23:59:59"
"Report Timings:","All Hours"
"10/31/2006","00:00:00"," -0.040"," -N- NONE"
"10/31/2006","00:15:00"," -0.037"," -N- NONE"
""
" ********************************************************************************"
" ******************************** End of Report *********************************"


Thanks in advance

-techiPA

 

[SQLSister]

Basically when I have had flat file info like this, I bring it all into a one column work table and then parse it out from there to my real work tables based onthe infformation in it. Inthis case you might want a two column table that has the seocnd column as an identity filed which you could then use as a line number. Then you could cursor through the information doing an insert for the first real line of info about a record and then updates for the other lines. When you reach the **** start a new record. or if a cursor would be too slow, since you are ggetting this frequently, you might want to try to use the line numbers to do massinserts and updates. Insert the *** and the line numbers first with nulls for the other fields you might have inthe second worktable. Then update the other fields by joining to the first work table on the line number+1, etc. for each update.

Not sure if this made any sense, I know what I'm trying to say but it is easier to show an example and I dont; have time to create one right now.


Not sure if I'm being clear here.

Questions about posting. See faq183-874

 

[techipa]

Hi SQLSister,

Thanks for the prompt reply. I partially understood your suggestion. may I know how I will get he source file.

The input file (csv) should be based on today's date. For example the name of the file is 'Daily_11-01-06.csv' then the DTS shoudl compare the date part and take the file which has today's date as source

Do you have any suggestion on how to implement this logic..

Thanks,
-techiPA

 

[techipa]

Hi All,

I need help in gettign the final output in the SQL table.

I have DTS package that imports the data to the SQL table.
This data is the temperature, Humidity readings taken for each room in 15 minutes of interval for 24 hours
From the first row I have to get the RoomNumber and ReadingType for all the rooms
Here is how the 'ABC' SQL table looks like

Col1 Col2 Col3
Point System: CPPS.PRIMARY.P01.BAPI RM TEMP NULL
Trend Every: 15 Min NULL
Date Range: 10/31/2006 - 10/31/2006 NULL
Report Timings: All Hours NULL
10/31/2006 00:00:00 70.71
10/31/2006 00:15:00 70.71
.
.
Point System: CPPS.PRIMARY.P01.LH.RMHUM NULL
Trend Every: 15 Min NULL
Date Range: 10/31/2006 - 10/31/2006 NULL
Report Timings: All Hours NULL
10/31/2006 00:00:00 27.94
10/31/2006 00:15:01 27.80
.
.
Point System: CPPS.PRIMARY.P02.BAPI RM TEMP NULL
Trend Every: 15 Min NULL
Date Range: 10/31/2006 - 10/31/2006 NULL
Report Timings: All Hours NULL
10/31/2006 00:00:00 70.09
10/31/2006 00:15:00 70.09
.
.
.

Now I need to insert this data in the final SQL table 'XYZ' in proper manner.
Should look like (C-Column refered from 'ABC' table)

RoomNum ReadingType Date Interval Reading
P01(C2) TEMP(C2) 10/31/2006(C1) 00:00:00(C2) 70.71C3)
P01(C2) RMHUM(C2) 10/31/2006(C1) 00:15:00(C2) 27.80C3)
P02(C2) TEMP(C2) 10/31/2006(C1) 00:30:00(C2) 70.09C3)
.
.
.

I would greatly appreciate your help.

 

[philhege]

So I take it the header from the text files ends up in your intake table. Ugh.

Given this environment, you're going to have to spin through the table (select it into a temporary table #tempwork for this, with a processed field (bit set to 0)) and perform the following steps:

set variables to hold each of the values to insert in the final table.

while any record in the temporary table still has a processed field value of 0,

select the top 1 record from the table where the processed value is 0.

If the first column value is "Point System", parse the name for the room value and store it to a variable (@l_vroom). Parse the type (RMHUM or TEMP) and store it to a variable
(@l_vtype)

If the first column can be evaluated to a date, that is, it is not "Point System" or "Trend Every", etc., insert the record to your final table:

INSERT INTO XYZ (RoomNum,ReadingType,Date,Interval,Reading)
VALUES(@l_vroom,@l_vtype,YourDateVariable,YourIntervalVariable,YourReadingVariable)

update the temporary table and set the processed value to 1 for the record you just read.

Next.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[AlexCuse]

techipa - might I steer you towards

http://www.sqldts.com?

No offense, but I get the feeling that you are rather new to working with DTS packages, and I think you would find this to be a tremendous resource. I use information found there very often, and it is rare that I run into a DTS problem that I can't find some kind of guidance on from that site.

Your task is a difficult one, and without knowing all of the ins and outs of your data, one that would be hard for anyone to make sense of.

One piece of advice I can give, is that it looks like you will need to go through the table you import this file to one row at a time, and handle the rows based on their contents in order to get the data into a table with a standard structure. This will be a LOT to figure out, even for someone working with the data on a daily basis. I am sorry I can't be of more help, but I wish you luck in your project. And once you are really into this, and you have more specific questions, I am sure that you will find tek-tips to be a great resource.

Good luck,

Alex



Ignorance of certain subjects is a great part of wisdom