default field type when importing

I asked this question in the .dts newsgroup and got no response. If someone
can help me here, that would be most appreciated.
I have a customer that does alot of importing of .txt files into SQL
databases. In the past, the default field type seemed to be varchar( 255).
Now, it seems that all the fields import as varchar( 8000 ), which messes up
some of their third-party programs that they use. So, whenever they import,
they have to go through every field and change it's type.
Does anyone know why this is happening and how I can change it back?
I have a feeling that this change occurred when I installed the MDAC
security update. Is this possible?
Thanks,
MikeWhat is your version of SQL Server. This is mine.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
Where do you see (8000). I just asked DTS to create a table for me from a
CSV and it created it as (255).
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> I asked this question in the .dts newsgroup and got no response. If
someone
> can help me here, that would be most appreciated.
> I have a customer that does alot of importing of .txt files into SQL
> databases. In the past, the default field type seemed to be varchar(
255).
> Now, it seems that all the fields import as varchar( 8000 ), which messes
up
> some of their third-party programs that they use. So, whenever they
import,
> they have to go through every field and change it's type.
> Does anyone know why this is happening and how I can change it back?
> I have a feeling that this change occurred when I installed the MDAC
> security update. Is this possible?
> Thanks,
> Mike
>|||Here's what I get:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you running
yours on a WinXP Pro workstation?
Once I have the source selected, there's a button with an (...) to view the
transform. That's where the varchar( 8000 ) can first be seen -- and
changed.
Have you kept up with all the Windows security updates? Including the MDAC
and JET updates that came out in the last couple of months?
My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
that got installed with the SQL Server 2000 SP3a.
Thanks for your help.
Mike
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> What is your version of SQL Server. This is mine.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> Where do you see (8000). I just asked DTS to create a table for me from a
> CSV and it created it as (255).
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > I asked this question in the .dts newsgroup and got no response. If
> someone
> > can help me here, that would be most appreciated.
> >
> > I have a customer that does alot of importing of .txt files into SQL
> > databases. In the past, the default field type seemed to be varchar(
> 255).
> > Now, it seems that all the fields import as varchar( 8000 ), which
messes
> up
> > some of their third-party programs that they use. So, whenever they
> import,
> > they have to go through every field and change it's type.
> >
> > Does anyone know why this is happening and how I can change it back?
> >
> > I have a feeling that this change occurred when I installed the MDAC
> > security update. Is this possible?
> >
> > Thanks,
> > Mike
> >
> >
>|||I am on WinXP yes
MDAC = 9.0.608.0 (Rollback version is the same as yours)
Ahhhhhhhhhhhh. I just spotted where you get your values from. You get them
in the wizard. Sorry I never use the wizard. I therefore do not know if
this is changed behaviour. I know that in the Designer i.e. building a
package without the wizard it is 255.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
news:OUlFMJJoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> Here's what I get:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
> happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you
running
> yours on a WinXP Pro workstation?
> Once I have the source selected, there's a button with an (...) to view
the
> transform. That's where the varchar( 8000 ) can first be seen -- and
> changed.
> Have you kept up with all the Windows security updates? Including the
MDAC
> and JET updates that came out in the last couple of months?
> My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
> that got installed with the SQL Server 2000 SP3a.
> Thanks for your help.
> Mike
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > What is your version of SQL Server. This is mine.
> >
> > Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> >
> > Where do you see (8000). I just asked DTS to create a table for me from
a
> > CSV and it created it as (255).
> >
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >
> > "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> > news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > > I asked this question in the .dts newsgroup and got no response. If
> > someone
> > > can help me here, that would be most appreciated.
> > >
> > > I have a customer that does alot of importing of .txt files into SQL
> > > databases. In the past, the default field type seemed to be varchar(
> > 255).
> > > Now, it seems that all the fields import as varchar( 8000 ), which
> messes
> > up
> > > some of their third-party programs that they use. So, whenever they
> > import,
> > > they have to go through every field and change it's type.
> > >
> > > Does anyone know why this is happening and how I can change it back?
> > >
> > > I have a feeling that this change occurred when I installed the MDAC
> > > security update. Is this possible?
> > >
> > > Thanks,
> > > Mike
> > >
> > >
> >
> >
>|||Thanks for your help. I'm not too familiar with DTS. I don't even know how
to get started in it. Can you do all the same things in the designer as in
the wizard?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:enEPzlJoDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I am on WinXP yes
> MDAC = 9.0.608.0 (Rollback version is the same as yours)
>
> Ahhhhhhhhhhhh. I just spotted where you get your values from. You get
them
> in the wizard. Sorry I never use the wizard. I therefore do not know if
> this is changed behaviour. I know that in the Designer i.e. building a
> package without the wizard it is 255.
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> news:OUlFMJJoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> > Here's what I get:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> > It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
> > happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you
> running
> > yours on a WinXP Pro workstation?
> >
> > Once I have the source selected, there's a button with an (...) to view
> the
> > transform. That's where the varchar( 8000 ) can first be seen -- and
> > changed.
> >
> > Have you kept up with all the Windows security updates? Including the
> MDAC
> > and JET updates that came out in the last couple of months?
> >
> > My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
> > that got installed with the SQL Server 2000 SP3a.
> >
> > Thanks for your help.
> > Mike
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > > What is your version of SQL Server. This is mine.
> > >
> > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > May 31 2003 16:08:15
> > > Copyright (c) 1988-2003 Microsoft Corporation
> > > Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> > >
> > > Where do you see (8000). I just asked DTS to create a table for me
from
> a
> > > CSV and it created it as (255).
> > >
> > >
> > > --
> > >
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > >
> > >
> > > "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in
message
> > > news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > > > I asked this question in the .dts newsgroup and got no response. If
> > > someone
> > > > can help me here, that would be most appreciated.
> > > >
> > > > I have a customer that does alot of importing of .txt files into SQL
> > > > databases. In the past, the default field type seemed to be
varchar(
> > > 255).
> > > > Now, it seems that all the fields import as varchar( 8000 ), which
> > messes
> > > up
> > > > some of their third-party programs that they use. So, whenever they
> > > import,
> > > > they have to go through every field and change it's type.
> > > >
> > > > Does anyone know why this is happening and how I can change it back?
> > > >
> > > > I have a feeling that this change occurred when I installed the MDAC
> > > > security update. Is this possible?
> > > >
> > > > Thanks,
> > > > Mike
> > > >
> > > >
> > >
> > >
> >
> >
>sql

DEC ODBC

Hi
I've got a DTS package that connects to a database on a DEC Vax, but I don't
have the ODBC driver. Anyone know where I can get a DEC ODBC Driver?
Thanks
JohnIt depends on what database. The following link lists ODBC
driver vendors:
http://www.sqlsummit.com/ODBCVend.htm
-Sue
On Thu, 11 Mar 2004 08:11:09 -0800, John Bandettini
<anonymous@.discussions.microsoft.com> wrote:

>Hi
>I've got a DTS package that connects to a database on a DEC Vax, but I don'
t have the ODBC driver. Anyone know where I can get a DEC ODBC Driver?
>Thanks
>John

Debugging DTS Errors

Hi,

I have a small import application that just calls a stored procedure which calls a DTS which imports a .csv file. the file usually has about 300 records and is created every day or so. Sometimes the DTS fails because one of the records has already been imported (primary key contraint). and the only error handling I have returns a message from the stored procedure if the DTS has failed or not. What I would like to be able to do is tell the user which line or record the DTS failed on. So that they can remove the line and import the file again. I don't want to just skip over the line in the DTS because it may have failed because there was an extra double quote or something like that. Any ideas?

Here is my code for the stored procedure which is calling the DTS:
CREATE PROCEDURE [dbo].[sp_Run_USPS_DTS]
@.strError nvarchar(100) OUTPUT

AS
DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
SELECT @.strError = 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, '\\DBServer\DTS_Files\USPSReports.dts', ''
IF @.hr <> 0
BEGIN
SELECT @.strError = 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
SELECT @.strError = 'Execute failed'
DECLARE @.MaxNum int
SELECT @.MaxNum = max(ReportNum) FROM USPS_Reports
DELETE FROM USPS_Export WHERE ReportNum = @.MaxNum
DELETE FROM USPS_Reports WHERE ReportNum = @.MaxNum
RETURN
END
GOI've implemented quite a number of data import processes, and its usually a multi-step process. What you need to do is separate out import file validation (the possible extra double quotes) from import file import..bringing the data into the dB.

Don't assume your data is good. Assume it is bad. Invoke a "validation" process and ensure it passes before you ever try to import the data. CSV files are great because you can write a little managed class that just opens and parses the file line by line looking for errors. You could even trigger the Stored Procedure to fire from within this framework only if validation passes.

Alex

Debugging and ActiveX Script Task in a DTS Package running on SQL Server 2005

I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.

I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?

Jay Abbott

I have not tried, but another way to stop execution is to use a MsgBox. Whilst it is paused, you could attach the debugger to the DTS process.

I would bin the ActiveX Script, or leave it in DTS. The diagnostic information is absolutely non-existent coming out of the SSIS ActiveX Script Task, and for my money that is unacceptable in a system. Leaving it in DTS is quite easy, and perhaps call the DTS from SSIS to allow you to migrate some functions in your process. Just the fact that no error information is forthcomming in the event of a failure is enough for me to avoid using it entirely.

Debug DTS

Hi,
I have a DTS package. I need to debug it, ANy ideas how?
Thanks,
Hi
Any use http://www.sqldts.com/303.aspx?
John
"Mecn" wrote:

> Hi,
> I have a DTS package. I need to debug it, ANy ideas how?
> Thanks,
>
>

Debug DTS

Hi,
I have a DTS package. I need to debug it, ANy ideas how?
Thanks,Hi
Any use http://www.sqldts.com/303.aspx?
John
"Mecn" wrote:
> Hi,
> I have a DTS package. I need to debug it, ANy ideas how?
> Thanks,
>
>