DTS Parsing Delimited Record

I am running SQLServer 2000 and am parsing a delimited file. Each line
is delimited with a crlf, but each line also ends with a tilda (~) that
I do not want to process. Each line does not have the same number of
elements, but every line ends with a tilda. Is there a way to set the
input file parser to strip off the tilda (that is, define the end of
line marker to be ~+cr+lf ?
Thanks,
greg lindstromhi,
why don't use each line as 'fixed field' from the beginning till the end and
then, using VbScript and substring do what you want.
--
current location: alicante (es)
"tuba_ranger" wrote:

> I am running SQLServer 2000 and am parsing a delimited file. Each line
> is delimited with a crlf, but each line also ends with a tilda (~) that
> I do not want to process. Each line does not have the same number of
> elements, but every line ends with a tilda. Is there a way to set the
> input file parser to strip off the tilda (that is, define the end of
> line marker to be ~+cr+lf ?
> Thanks,
> greg lindstrom
>

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie