DTS excel file

Hi,

I have to DTS a excel file to sql server 2000. The DTS package works fine. I have issue with one column that has value

450

800

900

45TH

23SI

800

390

100

30SI

If given the cell format general/text only that have alphanumeric characters are upload and not others. What should be done to upload all the values for this column.

thanks in advance.

Are you using DTS or SSIS?

There is a forum specific to DTS issues: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

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 and bit fields

sql2k sp2
If I dts data from a table to a .txt file all of the data
in the bit field gets turned into a "true" or "false"
value. Is there a way to turn this setting off?
TIA, ChrisDid not test, but
turn the column to allow null, then when dts, do not load the column.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris|||DTS a query or view rather than the base table.
In the query or view, use CONVERT or CAST to cast the bit column to integer
(or what you want).
James Hokes
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris

DTS "Can Not Insert Null Value"

Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.

DTS "Can Not Insert Null Value"

Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.

DTS - Excel conversion from Number to Database Char

I have to import data from Excel file to an SQL Server Database.
One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18.
Can you give me some help for the DTS.

Thanks,
PauloDoes the table already exists? Or are you letting DTS create it?|||The Table already exists. It's created each time the DTS run.

This is the script used on the DTS:
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Temp_freqnib]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Temp_freqnib]
GO

CREATE TABLE [dbo].[Temp_freqnib] (
[Cartao] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[numX] [char] (21) COLLATE Latin1_General_CI_AS NOT NULL ,
[desc] [varchar] (80) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
*/

It must be imported to the numX field.

Thanks,
Paulo|||my mistake...

The table exists only when the DTS runs, and it's created before the import of the data from excel.

Paulo

DtPicker Format

i have placed one date picker in a Vb form,while i select the value from datepicker the selected value is assigned to a textbox,while selection it is selecting in proper date format but my backend is SQL 2000,in backend it is storing the date as 1/1/1900 .00.00.00 ,so can help why this happens and send me the correct coding for that .

what i found out is while i select date between eg: 1/1/00 to 12/1/00 it will store in a correct format in backend,but if select 13/1/00 it won't store,i think it is taking in yyyy-dd-mm,i had given coding like this
insert into (regdate) values(' " & format(text1.text,"dd/mm/yy") & " ')"

can u help me for this by sending correct coding

thanks

saiju

saijumammen@.gmail.comTry this format

insert into (regdate) values(' " & format(text1.text,"dd-mmm-yyyy") & " ')"|||Dear Saiju
I had this problem , the best way to beat this is to change the date to "dd MMM yyyy" format and store it , Believe me it works

chk = format (dtpicker1.value,"dd MMM yyyy")
This is in a string format
Project it to the insert statement as a string and it works
This is to compliment what madhi has said|||Thank u ,Madhi & Rambi for the assistance for my Datepicker Problem.

This really helped me

Thanks once again

Saiju Mammen

DTEXEC /SET

Hello,

I have a command line as following, with DTEXEC to launch the execution of a package and to set a value (13335) of an user variable called CIB (string type):

dtexec /f c:\temp\PackageInsert.dtsx
/set \Package\DataFlowTask.Variables[Utilisateur::CIB].Properties[Value];13335

But I have got an error message saying that the object is not known in the package. My variable does exist in the variable window of the dataflow part.
Thank you for telling me what to set so that the variable can be set by the command line.

Regards,
Marie-Thrèse

I suspect the problem is the path in the set statement, check that the variable is scoped to the data flow task and notthe package for example. To help find the correct path, use the configurations wizard to set the variable value property, and just copy the path you get in there.

|||

Thank you so much for your advice. How should I do to use the configurations wizard, in order to get the correct path ?

|||

Marie-Thrèse wrote:

Thank you so much for your advice. How should I do to use the configurations wizard, in order to get the correct path ?

Every time you set up a configuration you have to tell it which object property you are talking about. On the last screen of the wizard the property paths are displayed.

-Jamie

|||

Thanks a lot Jamie for your advice