 | |
How do i do the following in DTS
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
How do i do the following in DTS -
06-09-2004
, 08:36 AM
#2
| |||
| |||
|
|
I have a table witrh a number of rows i.e. 12 rows of information as follows:- 101987 12345 Dan Smith 01256 33444 etc.. What i need to do is populate another table with the above data in columns : e.g 101987 12345 Dan Smith 01256 33444 etc.. Any ideas as i am at a loggerhead as how to do this in DTS as a Novice or near novice NOW!!! Thanks Ian |
#3
| |||
| |||
|
|
I have a table witrh a number of rows i.e. 12 rows of information a follows: 10198 1234 Dan Smit 01256 3344 etc. What i need to do is populate another table with the above data in column e.g 101987 12345 Dan Smith 01256 33444 etc. Any ideas as i am at a loggerhead as how to do this in DTS as a Novice o near novice NOW!! Thank Ia |
#4
| |||
| |||
|
|
Allan, the data on the source has a fixed number of rows i.e. there are 15 rows that need to be turned into different atrributes in a single row. so in |
in Column1 on the destination, read second row and place column1 in source
in column2 of destination and repeat for all 15 rows before cycling to the
next logical record in the destination table.
Quote:
|
If that makes sense does the use of rowset still apply and will try to get an example from somewhere as the book i have is like useless on rowset!! Cheers Ian ----- Allan Mitchell wrote: ----- So you have 1 attribute many rows That you want to turn into Many attributes 1 row. Do you have many destination rows in your source or just 1? How do you know when a row ends? How do you know the mappings? You can do this a number of way from 1. Reading the source into a recordset and then populating vriables and putting them into the destination to n. reading the rows as they come through in DTS, assign to variables and when you reach the magic end of row then insert to the destination. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Ian Clark" <iclark2001 (AT) hotmail (DOT) com> wrote in message news:2ED6A79E-1C9E-49A9-98E8-79D4D6D171FF (AT) microsoft (DOT) com... I have a table witrh a number of rows i.e. 12 rows of information as follows:- 101987 12345 Dan Smith 01256 33444 etc.. What i need to do is populate another table with the above data in columns : e.g 101987 12345 Dan Smith 01256 33444 etc.. Any ideas as i am at a loggerhead as how to do this in DTS as a Novice or near novice NOW!!! Thanks Ian |
#5
| |||
| |||
|
|
Allan the data on the source has a fixed number of rows i.e. there are 15 row that need to be turned into different atrributes in a single row. so i |
in Column1 on the destination, read second row and place column1 in sourc
in column2 of destination and repeat for all 15 rows before cycling to th
next logical record in the destination table
Quote:
|
If that makes sens does the use of rowset still apply and will try to get an example fro somewhere as the book i have is like useless on rowset! Cheer Ia ----- Allan Mitchell wrote: ---- So you hav 1 attribute many row That you want to turn int Many attributes 1 row Do you have many destination rows in your source or just 1 How do you know when a row ends How do you know the mappings You can do this a number of way fro 1. Reading the source into a recordset and then populating vriable an putting them into the destinatio t n. reading the rows as they come through in DTS, assign to variable an when you reach the magic end of row then insert to the destination -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP www.SQLDTS.com - The site for all your DTS needs I support PASS - the definitive, global communit for SQL Server professionals - http://www.sqlpass.or "Ian Clark" <iclark2001 (AT) hotmail (DOT) com> wrote in messag news:2ED6A79E-1C9E-49A9-98E8-79D4D6D171FF (AT) microsoft (DOT) com.. I have a table witrh a number of rows i.e. 12 rows of informatio a follows: 10198 1234 Dan Smit 01256 3344 etc. What i need to do is populate another table with the above data i column e.g 101987 12345 Dan Smith 01256 33444 etc. Any ideas as i am at a loggerhead as how to do this in DTS as Novice or near novice NOW!!! Thanks Ian |
#6
| |||
| |||
|
|
Tried this but comes up with some odd results - any idea of whether i am being a REAL Novice!!! or just a Wally '************************************************* ********************* ' Visual Basic Transformation Script '************************************************* *********************** DIM a,b,c,d,e,f,g,h,i Function Main() a=DTSSource("Col002") Main=DTSTransformStat_SkipInsert b=DTSSource("Col002") Main=DTSTransformStat_SkipInsert c=DTSSource("Col002") Main=DTSTransformStat_SkipInsert d=DTSSource("Col002") Main=DTSTransformStat_SkipInsert e=DTSSource("Col002") Main=DTSTransformStat_SkipInsert f=DTSSource("Col002") Main=DTSTransformStat_SkipInsert g=DTSSource("Col002") Main=DTSTransformStat_SkipInsert h=DTSSource("Col002") Main=DTSTransformStat_SkipInsert i=DTSSource("Col002") Main=DTSTransformStat_SkipInsert DTSDestination("Col001")=a DTSDestination("Col002")=b DTSDestination("Col003")=c DTSDestination("Col004")=d DTSDestination("Col005")=e DTSDestination("Col006")=f DTSDestination("Col007")=g DTSDestination("Col008")=h DTSDestination("Col009")=i main=DTSTransformStat_OK End Function ----- Allan Mitchell wrote: ----- Yuk OK The only real way I can see to do this is to read the values into variables. You have a counter which counts to 15. When it reaches 15 then all your variables should be full and you can then do an insert on the destination. Not pretty but then your data isn't either. Need anything else shout. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Ian Clark" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:54E55743-F8EE-47FE-A810-6C9BC8632A67 (AT) microsoft (DOT) com... Allan, the data on the source has a fixed number of rows i.e. there are 15 rows that need to be turned into different atrributes in a single row. so in effect i want to read the first record column 1 from the Source and place in Column1 on the destination, read second row and place column1 in source in column2 of destination and repeat for all 15 rows before cycling to the next logical record in the destination table. If that makes sense does the use of rowset still apply and will try to get an example from somewhere as the book i have is like useless on rowset!! Cheers Ian ----- Allan Mitchell wrote: ----- So you have 1 attribute many rows That you want to turn into Many attributes 1 row. Do you have many destination rows in your source or just 1? How do you know when a row ends? How do you know the mappings? You can do this a number of way from 1. Reading the source into a recordset and then populating vriables and putting them into the destination to n. reading the rows as they come through in DTS, assign to variables and when you reach the magic end of row then insert to the destination. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Ian Clark" <iclark2001 (AT) hotmail (DOT) com> wrote in message news:2ED6A79E-1C9E-49A9-98E8-79D4D6D171FF (AT) microsoft (DOT) com... I have a table witrh a number of rows i.e. 12 rows of information as follows:- 101987 12345 Dan Smith 01256 33444 etc.. What i need to do is populate another table with the above data in columns : e.g 101987 12345 Dan Smith 01256 33444 etc.. Any ideas as i am at a loggerhead as how to do this in DTS as a Novice or near novice NOW!!! Thanks Ian |
#7
| |||
| |||
|
#8
| |||
| |||
|
|
I have a table witrh a number of rows i.e. 12 rows of information as follows:- 101987 12345 Dan Smith 01256 33444 etc.. What i need to do is populate another table with the above data in columns : e.g 101987 12345 Dan Smith 01256 33444 etc.. Any ideas as i am at a loggerhead as how to do this in DTS as a Novice or near novice NOW!!! Thanks Ian My main suggestion here: how do you distinguish the beginning of each |
If there is a special delimiter that allows you to recognice a record,
you may just need to create a bcp FMT file where CRLF is the field
delimiter, and the other delimiter is the record delimiter. Voila,
you're done.
On the other hand, the only other reasonable configuration I can think
of for your data, let's say that every record is reliably 5 fields long,
and therefore 5 lines long. There are MANY solutions to this problem.
one fairly pure solution would be to BCP in all the data into fields
with a identity field initialized to start at 1, and another field with
varchar(255)
In another step, do a join which (using a modulus operator) and do a
self-join to the same table 4 extra times, with any necessary casts,
something like this:
select a.rectext
, convert(int,b.rectext)
, convert(smalldate, c.rectext)
, d.rectext
, e.rectext
from rawinput as a
join rawinput as b on a.recid + 1 = b.recid
join rawinput as c on a.recid + 2 = c.recid
join rawinput as d on a.recid + 3 = d.recid
join rawinput as e on a.recid + 4 = e.recid
where mod(a.recid,5) = 1
again, this is all from memory, and there may be bugs, so don't hold me
to any of this, but it would work.
Problems would be invalid dates for example. There are SQL functions to
validate dates prior to conversion. ISDATE, for example, may be a SQL
function, if I recall properly. Dates out of range would be another
concern, control characters embedded in text would be my most serious
concern. I've had users cut and paste data into my database
applications, with the result that varchar fields may contain embedded
CRLF's, tabs, and potentially end of file characters, /x00 characters,
and other potential "program killer" data values. this would invalidate
the "constant" 5 lines per record assumption, for example. I suppose you
could evaluate an entire set, for instance, in the example above, where
the third line of your 5 line record should contain the date, you could
validate the input data with a test like this:
select count(*) from rawdata where mod(recid,5) = 3 and not
isdate(rectext) or rectext not like
'[0-1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]'
(if I were expecting dates in exactly the format of the LIKE test, I
would still use an isdate test because some strings match the pattern
are still not dates.)
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.