DSXchange

All, Have a question on fixed width length file. The file has 30 columns. The last column has 12 bytes. So, we put Char(12). In the file there some records which have 11 bytes. So, if we checked suppress row truncation warning, its rejecting some records. If unchecked its aborting on the row where b...

Sounds like an ideal candidate for looping in a Transformer stage. Search for the next nn:nn:nn pattern, which signals the start of the next line. Or maybe search for the next colon character and parse around that. Any help on looping structure of the data? I am kind of confused on how to break it ...

All, I have posted this before and successfully parsed the data as shown below. Customer_id, Interaction_data Adam, 12:10:07: Dialing12:10:07: Connecting12:10:07: ABC: abc:9433769781$100.88.77.0:878712:10:07: ABCD: 000012:10:07: Agent Initializing12:10:18: On Call12:10:18: Assigned to operator12:10:...

Okay. Thanks Craig. It worked. Awesome..

Craig, Let me understand the design.

Currently, the design is
ODBC------
FTP Stage- Tranformer- ODBC

Reference odbc is same as target odbc where it updates old records and insert new records.

you want me to add one more link to update the records of lookup succeeds correct?

Thanks for such a valuable information Craig and Ray. I can actually create another job to update the rows whenever there is a change record. But lookup is always better. Will let you know if I have any issue. For now workaround.....Thanks again.

Thanks @bharathappriyan. which functions should I use, like

I need something that will update previous row's end date to yesterday's date (from default date) if the employee change the department and new record comes from source data.

All, Need a help on updating previous records with yesterdays date. We have table Employee detail, where we have employeeid, department, start_date, end_date. Sam, IT, 2014-07-25, 12/31/9999 Now, when the employee change the department, it should update the end_date to yesterdays date and insert sta...

Craig, I found a previous post with the code. Trying it. Thanks.

OK.Thanks Craig.

All, Have one more question. Can we convert a :SPACE delimiter to just pipe or any other delimiter. I am trying to use convert function in transformer, but not sure if COLONSPACE together can be converted with other delimiter. As an example, 12:10:07: Dialing 12:10:07: Connecting I want to replace :...

One way I found is, in server job sequential file stage there is a UNIX style (LF) Line termination. Which eventually breaking it to multiples lines.

So, from SEQ-Transform-Seq-Tranform-Seq,-- I can break one line to multiple.

That's true. We wanted to have atleast one delimiter. But the source table is a stand alone table so they cant change it. I am trying to break it in SQL server using common table expression. Lets see if it works.

Hi all, I have posted some part of this topic before.Not sure how to go with parsing of data. I have a single column Varchar(2000). I want to parse the row into multiple rows. Column looks like, 12:10:07: Dialing12:10:07: Connecting12:10:07: ABC: abc:9433769781$100.88.77.0:878712:10:07: ABCD: 000012...

Worked.... Thanks a lot everyone. Appreciate your help.