DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and eat
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu JaidkaHello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it freed
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:
> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely because
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> > Hi,
> >
> > Please let me know if there is any system stored procedure which can be
> > used
> > to delete the older versions of dts packages in SQL Server 2000 as in my
> > environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> > eat
> > up the disk space.
> >
> > Any other user SP(if no system SP is thr) if provided will also do.
> >
> > Thanks
> > Manu Jaidka
>
>

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and ea
t
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu JaidkaHello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it free
d
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:

> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely becaus
e
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
>
>sqlsql

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and eat
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu Jaidka
Hello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka
|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it freed
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:

> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely because
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
>
>

DTS of data between two tables, same field names but differnt datatypes

I need to migrate data from one sql database to another. The second DB
is a newer version of the "old" database with mostly the same tables
and fieldnames. In order support some reporting queries in the "new"
version I needed to change the datatype of a few fields from varchar to
int(the data stored was integers already as they were lookup tables).

DTS works great except in the cases of about 10 fields which I changed
the datatypes on from varchar to int.

DTS seems to drop the data if the fieldname and datatype are not an
exact match. Is there any way to use DTS and have it copy data from a
field call subsid type varchar to a field call subsid type int?<tdmailbox@.yahoo.com> wrote in message
news:1115388735.044389.231150@.g14g2000cwa.googlegr oups.com...
>I need to migrate data from one sql database to another. The second DB
> is a newer version of the "old" database with mostly the same tables
> and fieldnames. In order support some reporting queries in the "new"
> version I needed to change the datatype of a few fields from varchar to
> int(the data stored was integers already as they were lookup tables).
> DTS works great except in the cases of about 10 fields which I changed
> the datatypes on from varchar to int.
> DTS seems to drop the data if the fieldname and datatype are not an
> exact match. Is there any way to use DTS and have it copy data from a
> field call subsid type varchar to a field call subsid type int?

This should work fine - I tested it quickly on two tables with the same
column name but the source being varchar(10) and the destination int. Using
a Transform Data Task worked correctly, and the mapping was handled
automatically.

I'm not sure what you mean by DTS seems to "drop the data". Perhaps you can
give some more information - your MSSQL version, which type of task you're
using to move the data, the DDL for the tables, any error messages etc.

Simon|||DTS copies all the columns except the ones with datatype changes. My
varchars to ints end up with empty ints in the desitnation table.

SQL 2000, DTS export, no error messages.|||I tested it again with the export wizard (I'd used the package designer
before), and it worked fine. I have no idea why it's not working for
you, unless you have a column transformation of some sort. One other
guess would be that your values are too big for an int, and you've also
set ANSI_WARNINGS OFF, which would ignore the overflow. But since
that's ON for ODBC/OLE DB by default, it's very unlikely.

You could specify a source query instead of a source table and use CAST
to force the conversion:

select col1, col2, cast(col3 as int), col4...
from dbo.SourceTable

Even if that doesn't work, you might get a clue of some sort from an
error. Finally, you could also copy the data into a staging table which
has exactly the same structure as the source table, then use SQL to
check and INSERT the data. Or just INSERT directly, if both databases
are on the same server.

Simon

DTS not able to open

Hi experts,
i have faced one issue with DTS, my office SQL server is 2000 x86
version, but my labtop using SQL Server managment studio, when i try
to expend the office SQL DTS package, which show me "this package
cannot be edited" and the following msg is "SQL server 2005 DTS
Designer components are required to edit DTS packages", "Install the
special Web download, SQL Server 2000 DTS designer components to use
this feature". however my co-worker are able to open, this is
something special , and my SQL studio setting almost same as him.
my labtop detail:
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600
pls advise, many thanksHi
Check out the information on
http://technet.microsoft.com/en-us/library/ms143706.aspx for more
information. If you need to edit a DTS package you will need to download the
Microsoft SQL Server 2000 DTS Designer Components from the features pac
http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17
John
"XJ" wrote:
> Hi experts,
> i have faced one issue with DTS, my office SQL server is 2000 x86
> version, but my labtop using SQL Server managment studio, when i try
> to expend the office SQL DTS package, which show me "this package
> cannot be edited" and the following msg is "SQL server 2005 DTS
> Designer components are required to edit DTS packages", "Install the
> special Web download, SQL Server 2000 DTS designer components to use
> this feature". however my co-worker are able to open, this is
> something special , and my SQL studio setting almost same as him.
> my labtop detail:
> Microsoft SQL Server Management Studio 9.00.3042.00
> Microsoft Analysis Services Client Tools 2005.090.3042.00
> Microsoft Data Access Components (MDAC) 2000.085.1117.00
> (xpsp_sp2_rtm.040803-2158)
> Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
> Microsoft Internet Explorer 7.0.5730.13
> Microsoft .NET Framework 2.0.50727.832
> Operating System 5.1.2600
>
> pls advise, many thanks
>

DTS migration wizard, where does it put migrated version?

From SQL Server Management Studio I right click on Management/Legacy/DTS and select Migration Wizard and enter a SQL Server Source and Destination. A list of DTS packages from the source server is displayed and I select one of the DTS packages and give the destination a unique name. The wizard runs and says it's successful. I can't find the new SSIS version of the DTS package with the new name that I've given it, however. Where does it put the new migrated copy of the DTS package.

Thanks,

John

In Sql Management Studio, under the Object Explorer window in the Connect drop down choose Integration Services, and it will show you all stored packages on that instance.|||Thanks, this helps. Now I'd like to find out how to edit the migrated version. How to I get to it from BI Dev Studio?|||

All of the stored packages will usually be in ~\Program Files\Microsoft SQL Server\90\DTS\Packages

DTS issue

Hello,
I am using DTS (the version that shipped with MSSQL 7.0).
I am trying to import at space-delimited text file into a database table.
I have one column in my text file which tends to exced 255 characters.
When this occurs, I run into the following error during the import:
'data for source column 3(colname3) is too large for the specified buffer size.'
Microsoft recommended changing the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows from 8 to 0.
This did not work.
Any suggestions for defeating this error would be greatly appreciated.

Thanks,
JeffJust a guess, but is the registry key relevant? It looks like something Excel-specific.