DTS Fails

This summary is not available. Please click here to view the post.

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--greg
Hi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:

> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>
sql

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--gregHi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:

> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--gregHi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:
> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>

dts conversion error

I have a dts package that imports data from a comma delimited .csv file.

I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).

So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.

How can I make the conversion correctly during the dts execution.

Thank you.do you have any empty strings in the source file going to the float column?|||Also - does your CSV include column headings and, if so, have you ticked the box in the DTS to let SQL Server know that?sql

DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:

>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'***************************************
*******************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'***************************************
*********************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:[vbcol=seagreen]
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
[vbcol=seagreen]
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
> '***************************************
*******************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
> '***************************************
*********************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:|||Thanks!
Sue Hoegemeier wrote:[vbcol=seagreen]
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>

DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:
>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
> >I am trying to DTS some data from delimited source and some of the
> >dates have the value 99999999. This naturally makes the DTS job fail.
> >Is there a method to make it ignore these values and append only those
> >with acceptable date formats?: 20060810
> >
> >Thanks for any suggestions.
> >
> >RBollinger|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
>'**********************************************************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
>'************************************************************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:
>> There are probably quite a few different ways...it all
>> depends as to what best meets the overall needs for the
>> package.
>> One option would be to use a query against the text source
>> to select the rows you want to import using whatever
>> criteria against the date column.
>> Another would be to use an ActiveX Transformation script
>> with the Transform Data task, check the value of the column
>> with the date value and if it's not a date or in whatever
>> format, use DTSTransformStat_SkipInsert
>> to skip the row.
>> And another option would be to import the data into a
>> staging table, use a transform data task and query the
>> staging table for rows with the appropriate values for the
>> date column.
>> -Sue
>> On 10 Aug 2006 14:09:57 -0700, "robboll"
>> <robboll@.hotmail.com> wrote:
>> >I am trying to DTS some data from delimited source and some of the
>> >dates have the value 99999999. This naturally makes the DTS job fail.
>> >Is there a method to make it ignore these values and append only those
>> >with acceptable date formats?: 20060810
>> >
>> >Thanks for any suggestions.
>> >
>> >RBollinger|||Thanks!
Sue Hoegemeier wrote:
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
> >Making a staging table isn't an option for me so I'll use the
> >Transformation script:
> >
> >The source is a text file and I am having difficulty with the date.
> >The script is as follows (it may wrap):
> >
> >'**********************************************************************
> >' Visual Basic Transformation Script
> >' Copy each source column to the
> >' destination column
> >'************************************************************************
> >
> >Function Main()
> > DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> >
> > if DTSSource("Col002") = "99999999" then
> > Main = DTSTransforStat_SkipRow
> > else
> > DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
> >DTSSource("Col002"))
> > end if
> >
> > DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> > DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> > DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> > DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
> >Main = DTSTransformStat_OK
> >End Function
> >
> >This is bombing with a type mismatch 'CONVERT' error. Any suggestions
> >appreicated.
> >
> >RBollinger
> >
> >
> >
> >
> >
> >
> >
> >Sue Hoegemeier wrote:
> >> There are probably quite a few different ways...it all
> >> depends as to what best meets the overall needs for the
> >> package.
> >> One option would be to use a query against the text source
> >> to select the rows you want to import using whatever
> >> criteria against the date column.
> >> Another would be to use an ActiveX Transformation script
> >> with the Transform Data task, check the value of the column
> >> with the date value and if it's not a date or in whatever
> >> format, use DTSTransformStat_SkipInsert
> >> to skip the row.
> >> And another option would be to import the data into a
> >> staging table, use a transform data task and query the
> >> staging table for rows with the appropriate values for the
> >> date column.
> >>
> >> -Sue
> >>
> >> On 10 Aug 2006 14:09:57 -0700, "robboll"
> >> <robboll@.hotmail.com> wrote:
> >>
> >> >I am trying to DTS some data from delimited source and some of the
> >> >dates have the value 99999999. This naturally makes the DTS job fail.
> >> >Is there a method to make it ignore these values and append only those
> >> >with acceptable date formats?: 20060810
> >> >
> >> >Thanks for any suggestions.
> >> >
> >> >RBollinger

DTS - Import Text file - varying column count

I receive a pipe delimited file without headers, and a header and trailer
row added as a check to each file. When I say header I don't mean column
headings, I mean a row of information that verifies the contents of the file
(example below)
The header info contains 5 columns, trailer info contains 7 columns, and the
normal data contains 28 columns.
The import succeeds when there is data (the header/trailer rows inserts NULL
into the remaining columns which is fine), however when there are no rows,
only the header and trailer rows are in the file. This fails as the columns
from 8 onwards are not there.
Firstly, can I make the DTS not "fail" if the full number of columns are
present? Currently I'm using "on completion" at the upload which continues
the DTS processing, but it fails at the end.
Second option - can I do a quick check to see if there are only 2 rows in
the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
work, then delete the NULL row?
example file:
IMPORT_HEADER|HR|2006|04|18
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
IMPORT_TRAILER|TR|2006|04|18|3|123.99
Thanks,Hi
You may find better advice in the DTS newsgroup
microsoft.public.sqlserver.dts , but you may be able to use an activeX
transformation to determine if you are reading a header row, data row or
footer assuming that the first column defines the row type.
Check out http://www.sqldts.com/default.aspx?279 and possibly
http://www.sqldts.com/default.aspx?266 and
http://www.sqldts.com/default.aspx?282. You should also be able to skip the
insertion of the header and trailer by returning DTSTransformStat_SkipInsert
or DTSTransformStat_NoMoreRows (if the row is the footer or the row is the
header and it says there are no rows within the file!) to make it cleaner
see the topic "DTSTransformStatus" in Books online for more.
John
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:OM91g.40851$Ph2.9628@.newsfe4-gui.ntli.net...
>I receive a pipe delimited file without headers, and a header and trailer
>row added as a check to each file. When I say header I don't mean column
>headings, I mean a row of information that verifies the contents of the
>file (example below)
> The header info contains 5 columns, trailer info contains 7 columns, and
> the normal data contains 28 columns.
> The import succeeds when there is data (the header/trailer rows inserts
> NULL into the remaining columns which is fine), however when there are no
> rows, only the header and trailer rows are in the file. This fails as the
> columns from 8 onwards are not there.
> Firstly, can I make the DTS not "fail" if the full number of columns are
> present? Currently I'm using "on completion" at the upload which continues
> the DTS processing, but it fails at the end.
> Second option - can I do a quick check to see if there are only 2 rows in
> the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
> work, then delete the NULL row?
> example file:
> IMPORT_HEADER|HR|2006|04|18
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> IMPORT_TRAILER|TR|2006|04|18|3|123.99
> Thanks,
>

DTS - help

Create a table (each time different table) we have a DTS to do that
-- 1
There is a fixed delimited text file; we need to Import this file into
the created table above and we have another DTS to do that. 2

We want to combine these two DTS into one.

The problem is when the table does not exist it will not show in the
drop down list for DTS 2.

Is there a way we can pass the table name as a variable name from DTS
1 to DTS 2. Only for 2, we need a DTS. For 1, it can be a stored
procedure or anything.

My main question is if there is a way to pass the variable name (table
name) to DTS2?

Thank you very much for your help in advance.You can assign the table name to a DTS global variable and then use a
dynamic properties task to set DTS object properties to the global variable
value.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Geetha" <gelangov@.hotmail.com> wrote in message
news:4b40e20a.0410280621.cfce123@.posting.google.co m...
> Create a table (each time different table) - we have a DTS to do that
> -- 1
> There is a fixed delimited text file; we need to Import this file into
> the created table above and we have another DTS to do that. -2
> We want to combine these two DTS into one.
> The problem is when the table does not exist it will not show in the
> drop down list for DTS 2.
> Is there a way we can pass the table name as a variable name from DTS
> 1 to DTS 2. Only for 2, we need a DTS. For 1, it can be a stored
> procedure or anything.
> My main question is if there is a way to pass the variable name (table
> name) to DTS2?
> Thank you very much for your help in advance.