DTS - Execute Data-pump Task

SQL 2K
Using Active X script , created the source SQL statement for data-pump task
and it worked.
Inside Active X script I am looping thru to change the source SQL
statements, how can I again execute this data-pump task.
When I look the source SQL in data-pump task it got changed , but data-pump
task executed only once.
So my question is, how to execute a data-pump task inside Active-X script '
Thx
DUWhat are you using to determine your new connections ?
Why do you need to reset this during the package?
You can easily loop to do this but you have to loop on something.
How are you calling the package ?
Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/Default.aspx?298)
--
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
"MS User" <sqlman@.sql.com> wrote in message
news:%233nQmIbnDHA.744@.tk2msftngp13.phx.gbl...
> SQL 2K
> Using Active X script , created the source SQL statement for data-pump
task
> and it worked.
> Inside Active X script I am looping thru to change the source SQL
> statements, how can I again execute this data-pump task.
> When I look the source SQL in data-pump task it got changed , but
data-pump
> task executed only once.
> So my question is, how to execute a data-pump task inside Active-X script
'
> Thx
> DU
>

DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this '
Thanks In Advance
JeffYou would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this '
> Thanks In Advance
> Jeff
>|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas '
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas '
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>> You would probably want to have a look at these two articles then.
>> Looping, Importing and Archiving
>> (http://www.sqldts.com/default.aspx?246)
>> How to loop through a global variable Rowset
>> (http://www.sqldts.com/default.aspx?298)
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "MS User" <sqlman@.sql.com> wrote in message
>> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>>
>|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> Thanks Allan
>> My Excel sheet contains different sheets and I just want to read one
>> sheet to load into table. Thoughts/Ideas '
>> Thanks
>>
>> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
>> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>> You would probably want to have a look at these two articles then.
>> Looping, Importing and Archiving
>> (http://www.sqldts.com/default.aspx?246)
>> How to loop through a global variable Rowset
>> (http://www.sqldts.com/default.aspx?298)
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "MS User" <sqlman@.sql.com> wrote in message
>> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>>
>>
>

DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this ?
Thanks In Advance
Jeff
You would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this ?
> Thanks In Advance
> Jeff
>
|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas ?
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>
|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas ?
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>

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

DTS - Error

Hi,

I encouter a mystic behaviour:

1. When I am starting my DTS-package (using "other ODBC-Device" for connecting) by hand, it runs always reliable.

2. When I schedule this task, it gets always the following error:

Executed as user: ACB\Dienst-SQL. ...nStart: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step DTSRun OnError: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step DTSRun OnStart: DTSStep_OMWCustomTasks.OMWTransferErrors_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSSt... Process Exit Code 12. The step failed.
:(

Can anybody explain me the different handling of a scheduled task and a task startet manually ?

Thx in advanceI would guess your dts package is using a user dsn to make one of your connections. When you schedule the job you are not running as the same user when you execute the package interactively and therefore you can not file the dsn.

If this is the case I think changing the dsn to a system dsn will fix the problem.

If you are not doing this then you'll need to give more information about how that specific connect is defined.

DTS - Dr Watson Error

I had an error crop up with a DTS package which I need some help with.

Error Message :

The application, , generated an application error The error occurred
on 07/20/2004 @. 06:12:28.203 The exception generated was c0000005 at
address 41504E2C (<nosymbols>)

This happened this morning, but the DTS package runs every 10 minutes
and it is the only occurance of this error. Nothing in the DTS package
has changed and there will not have been any users on the network. I'm
as convinced as I can be that the DTS package is fine. Nothing obvious
as to what has caused the error, and it hasn't happened again, but I
would like to understand it more if possible.

The closest answer we found was for a SQL2000 error (
http://support.microsoft.com/defaul...en-us%3BQ290644 ),
but we are running SQL7 (latest sp), so any fix wouldn't apply.

The DTS package does a fair amount of importing data and then various
SP's are run to generate a final table of data. From the timing, it
looks to have happened at the end of the process, so would suggest
something with an SP. Unfortunately, I cannot tell which one. 10
minutes after it failed, it worked again and has since, but no-one was
in to correct anything.

Any ideas ?

Thanks

RyanOn further investigation, it looks to have got to right at the end of
the DTS package and then failed. The second to last task outputs a
date / time value along with a record count, which has worked. After
that is a bit of code to dump the transaction log and the scheduled
task should e-mail if it fails. I'm wondering if an exchange server
problem has caused this ? We do have a few issues with Exchange, but
these should have been sorted.

Is this a likely explanation ?

DTS - Copy Sql Server Objects help

Im trying to create a DTS Package to copy my sql Server objects to a test Server. The server Im copying from is UMTS1 and the server Im copying to is UMTSDEV.
The database name is ProgramSpecs and exists on bother servers. My login is assigned to all server roles on both servers. I have created databases on both servers manually so Im pretty sure I have all the necessary permissions. Im using the DTS task Copy Sql Server Objects to copy sql server objects and have selected Drop Destination objects first.

When I try to execute the package I get the following error:
Error source: MS SQL DMO
Error Description: Invalid OLEVERB Structure [SQL DMO] create file error or UMTS1.ProgramSpecs.LOG

Can anyone tell me what Im doing wrong?

Thanks
GEMDTS writes the necessary script files at C:\Program Files\Microsoft SQL Server\80\Tools (for sql2k provided u havent changed installation folders). check the content of the file UMTS1.ProgramSpecs.LOG at that folder. u may get the clue.|||I looked in C:\Program Files\Microsoft SQL Server\80\Tools and was unable to find the file you mentioned. I looked in the directory on my local drive and on the server and was unable to find the file. The installation folders for SQL Server haven't been changed from the default during the installation.|||Are the specs exactly the same on both servers? Including collation?

You can find this out by right-clicking on the database in Enterprise Manager and select Properties. They should match.|||I looked in C:\Program Files\Microsoft SQL Server\80\Tools and was unable to find the file you mentioned. I looked in the directory on my local drive and on the server and was unable to find the file. The installation folders for SQL Server haven't been changed from the default during the installation.

ok, if not in the folder i mentioned, check the "Script file directory" textbox in the "copy" tab of the DTS step. you must find the file there. at times missing dependent objects causes the copy to fail.

alternatively u can use backup/restore to make a copy of a database. its easy.|||I was going to say - why are we DTS'ing this when we could backup and restore to the test environment?
That's what we do over here and it's never caused us any problems :)|||I want to use the DTS package so I can select which objects to copy. I have already converted my initial data from an MS Access database which was a real chore. I m now in the process of creating a website and stored procedures. I think if I just do a restore database, I would loose all of the stored procedures Im in the process of creating. So unless I duplicated any new store procedures I developed in the other database I would loose them when I restored. If I copied the objects using DTS I could only copy the tables and data.

Another option I have considered is to create a second database that only had views and stored procedures that referenced the original database.
Example for northwind:
Northwind (original Database)
Northwind_Web (Web Database)

Nortwind has a table called tblEmployees(I think). The database Northwind_web would have a VIEW called tblEmployees and the select statement would be Select * from Northwind.dbo.tblEmployees. All of my updates, deletions and appends would be done through the views. Then when I got ready to go live all I would have to do is script the procs from Northwind_web to Northwind and it should work because instead of updating views, the tables would automatically be updated because the names are the same.

Another option I have thought about is to simply use Update Northwind.dbo.TblEmployees instead of just update tblEmployees in my procs . Either way I would have a separate database; one for just the data and one for the views and stored procedures that updated the original database. Then I could just restore the Northwind database when I wanted to refresh the data.

My questions are: How stupid is this? What would the affect on performance be having 2 databases? If I decide to go with a restore only, can you restore only data without the stored procedures and views?

DTS - ActiveXScripting Not Working on 64bit AMD Cluster on SQL Server 2005 SP2

I have a problem with DTS - ActiveXScripting that it cannot initialize the script engine, I have the backward compatibility stuff installed, even tried a repair but still does not work.

Many thanks for any help you can provide on this problem.

Paul

Error Information below:

Date 17/04/2007 13:07:32
Log Job History (Test ActiveX Script)

Step ID 1
Server GBCONV1A002V03
Job Name Test ActiveX Script
Step Name Step 1: Exec TestActiveX script
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: WW004\ConSrvSQLAgentA002. ...TSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220487 (800403F9) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220487 (800403F9); Provider Error: 0 (0) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSActiveScriptTask_1' failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: ... Process Exit Code 1. The step failed.

Try asking in the DTS usenet group.

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

DTS - Access Commection Properties

Does anyone know what the different values are (and what they mean) on the "Database Locking Mode" column off of the advanced button on a microsoft access connection within a DTS package on SQL server 7.0 ?

It's a 4 byte signed integer type with a default value of zero

Cheers,

DuncanMaybe this (http://www.able-consulting.com/tech.htm) will help?

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS

I have created a Data Transformation Service (DTS) in SQL Server using the import/export data wizard, saved it as a Visual Basic file and then upgraded it to Vb.Net.

I am importing data from an excel file into a SQL Server table.
The basics are working fine, but I now want to improve the importing function.

I want to disallow duplicate entries so that the same data cannot be entered more than once - except for if the data for a specific record has changed - and in this case I want to add the record, and move its original value to another table.

Does anyone have an idea how I can go about this?/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
idname
1a
2b
3c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

http://www.sqldts.com/default.aspx?271

Try both the above code and the url for options, to disallow duplicates make your table UNIQUE INDEX compatible because SQL Server will not allow the creation of UNIQUE INDEX on Columns that already include duplicate values. Include IGNORE_DUP_KEY in your create INDEX statement. Hope this helps

Kind regards,
Gift Peddie|||I am not sure how to implement this code within the code I already have.
I want to put this code behind the click event of a button in a .aspx.vb file.

How do I implement what you suggested within my code?

Option Strict Off
Option Explicit On
Module TestingOrders
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: H:\TestingOrders.bas
'Package Name: TestingOrders
'Package Description: DTS package description
'Generated Date: 18/01/2005
'Generated Time: 11:45:36
'****************************************************************
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Sub Main()
goPackage = goPackageOld
goPackage.Name = "TestingOrders"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
Dim oConnProperty As DTS.OleDBProperty
'-----------------------
' create package connection information
'-----------------------
Dim oConnection As DTS.Connection2
'---- a new connection defined below.
'For security purposes, the password is never scripted
oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
 'File name here equal to user's choice of file
oConnection.ConnectionProperties.Item("Data Source").Value = "H:\Orders.xls"
oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;"
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "H:\Orders.xls"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
'UPGRADE_WARNING: Couldn't resolve default property of object oConnection. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Connections.Add(oConnection)
'---- a new connection defined below.
'For security purposes, the password is never scripted
oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "101032844"
oConnection.ConnectionProperties.Item("Data Source").Value = "INDUS"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "INDUS"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "101032844"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
'UPGRADE_WARNING: Couldn't resolve default property of object oConnection. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Connections.Add(oConnection)
'-----------------------
' create package steps information
'-----------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'---- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Step"
oStep.Description = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
'UPGRADE_WARNING: Couldn't resolve default property of object oStep. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Steps.Add(oStep)
'-----------------------
' create package tasks information
'-----------------------
'---- call Task_Sub1 for task Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task (Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task)
Call Task_Sub1(goPackage)
'-----------------------
' Save or execute package
'-----------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Steps.Item. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
 tracePackageError(goPackage)
goPackage.Uninitialize()
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
End Sub
'------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'------------------------
Public Sub tracePackageError(ByRef oPackage As DTS.Package)
Dim ErrorCode As Integer
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Integer
Dim ErrorIDofInterfaceWithError As String
Dim i As Short
For i = 1 To oPackage.Steps.Count
If oPackage.Steps.Item(i).ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oPackage.Steps.Item(i).GetExecutionErrorInfo(ErrorCode, ErrorSource, ErrorDescription, ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError)
MsgBox(oPackage.Steps.Item(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription)
End If
Next i
End Sub
'---- define Task_Sub1 for task Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task (Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task)
 Public Sub Task_Sub1(ByVal goPackage As DTS.Package2)
 Dim oTask As DTS.Task
 Dim oLookup As DTS.Lookup
 Dim oCustomTask1 As DTS.DataPumpTask2
 'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
 oTask = goPackage.Tasks.New("DTSDataPumpTask")
 oTask.Name = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
 oCustomTask1 = oTask.CustomTask
 oCustomTask1.Name = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
 oCustomTask1.Description = "Copy Data from Sheet1$ to [101032844].[101032844].[TestingOrders] Task"
 oCustomTask1.SourceConnectionID = 1
 'SQL Statement here
 'Eliminate duplicates here
 'Create a test table to load the data into
 'Create a duplicate/archive table to move duplicates to from original table
 oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "
 oCustomTask1.DestinationConnectionID = 2
 oCustomTask1.DestinationObjectName = "[101032844].[101032844].[TestingOrders]"
 oCustomTask1.ProgressRowCount = 1000
 oCustomTask1.MaximumErrorCount = 0
 oCustomTask1.FetchBufferSize = 1
 oCustomTask1.UseFastLoad = True
 oCustomTask1.InsertCommitSize = 0
 oCustomTask1.ExceptionFileColumnDelimiter = "|"
 oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
 oCustomTask1.AllowIdentityInserts = False
 oCustomTask1.FirstRow = 0
 oCustomTask1.LastRow = 0
 oCustomTask1.FastLoadOptions = 2
 oCustomTask1.ExceptionFileOptions = 1
 oCustomTask1.DataPumpOptions = 0
 Call oCustomTask1_Trans_Sub1(oCustomTask1)
 'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
 goPackage.Tasks.Add(oTask)
 End Sub
 Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As DTS.DataPumpTask2)
 Dim oTransformation As DTS.Transformation2
 Dim oTransProps As DTS.Properties
 Dim oColumn As DTS.Column
 'UPGRADE_WARNING: Couldn't resolve default property of object oCustomTask1.Transformations. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
 oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
 oTransformation.Name = "DirectCopyXform"
 oTransformation.TransformFlags = 63
 oTransformation.ForceSourceBlobsBuffered = 0
 oTransformation.ForceBlobsInMemory = False
 oTransformation.InMemoryBlobSize = 1048576
 oTransformation.TransformPhases = 4
 oColumn = oTransformation.SourceColumns.New("Order No", 1)
 oColumn.Name = "Order No"
 oColumn.Ordinal = 1
 oColumn.Flags = 118
 oColumn.Size = 0
 oColumn.DataType = 5
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.SourceColumns.Add(oColumn)
 oColumn = oTransformation.SourceColumns.New("Country", 2)
 oColumn.Name = "Country"
 oColumn.Ordinal = 2
 oColumn.Flags = 102
 oColumn.Size = 255
 oColumn.DataType = 130
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.SourceColumns.Add(oColumn)
 oColumn = oTransformation.SourceColumns.New("Desc", 3)
 oColumn.Name = "Desc"
 oColumn.Ordinal = 3
 oColumn.Flags = 102
 oColumn.Size = 255
 oColumn.DataType = 130
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.SourceColumns.Add(oColumn)
 oColumn = oTransformation.SourceColumns.New("Amount", 4)
 oColumn.Name = "Amount"
 oColumn.Ordinal = 4
 oColumn.Flags = 118
 oColumn.Size = 0
 oColumn.DataType = 5
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.SourceColumns.Add(oColumn)
 oColumn = oTransformation.DestinationColumns.New("Order_Number", 1)
 oColumn.Name = "Order_Number"
 oColumn.Ordinal = 1
 oColumn.Flags = 120
 oColumn.Size = 10
 oColumn.DataType = 129
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.DestinationColumns.Add(oColumn)
 oColumn = oTransformation.DestinationColumns.New("Country", 2)
 oColumn.Name = "Country"
 oColumn.Ordinal = 2
 oColumn.Flags = 120
 oColumn.Size = 10
 oColumn.DataType = 129
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.DestinationColumns.Add(oColumn)
 oColumn = oTransformation.DestinationColumns.New("Description", 3)
 oColumn.Name = "Description"
 oColumn.Ordinal = 3
 oColumn.Flags = 120
 oColumn.Size = 10
 oColumn.DataType = 129
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.DestinationColumns.Add(oColumn)
 oColumn = oTransformation.DestinationColumns.New("Amount", 4)
 oColumn.Name = "Amount"
 oColumn.Ordinal = 4
 oColumn.Flags = 120
 oColumn.Size = 10
 oColumn.DataType = 129
 oColumn.Precision = 0
 oColumn.NumericScale = 0
 oColumn.Nullable = True
 oTransformation.DestinationColumns.Add(oColumn)
 oTransProps = oTransformation.TransformServerProperties
 'UPGRADE_WARNING: Couldn't resolve default property of object oCustomTask1.Transformations. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1037"'
 oCustomTask1.Transformations.Add(oTransformation)
 End Sub
End Module

|||I don't write VB but I think you are using the code to eliminate duplicates, the UNIQUE index will do it if the IGNORE_DUPLICATES is added to the create index statement. The link below has a working DTS Package with code in VB6 you can consume it as COM object by adding Reference to it in your code. The linked server code can be called as stored proc. I also found very detailed tutorial using Excel object Model by Ken Getz on MSDN, he is a member of the International .NET user groups speakers. Hope this helps.

http://www.sqldts.com/default.aspx?t=6&s=101&i=243&p=1&a=0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp

Kind regards,
Gift Peddie

DTS

I need to get a list of tables from a tabl, then loop through the table list and execute a stored proc feeding it the table name as a parameter...

How could this best be done in DTS?

Jim

JimDi wrote:

I need to get a list of tables from a tabl, then loop through the table list and execute a stored proc feeding it the table name as a parameter...

How could this best be done in DTS?

Jim

I think this will help:

Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx)

-Jamie

Dts

Hi,
I'm using a DTS package to import data from a Access database to sql server.But the problem is the name of the database will be different all the time.Can I give the name of thedatabase dynamically from the front end.Is there any provision in sql server for that
Waiting fot the reply
thanx in advance
regards
NebuPerhaps when you create a package that includes a dynamic properties task which sets the filename of the access connection prior its executing. However, you need a way to determine which filename is next. Perhaps a better alternative is to rename the access-database file instead and go on from that point.|||You could add an ActiveXScript Task to your DTS that uses the InputBox fucntion inVBScript. This would prompt you for a file name when you run the DTS package. You can then store the input in a package global var and use that via a Dynamic Properties task to set the file your data connector is pointing at.

regards

Steve|||Can u give more specific answer pls

Dts

I am trying to copy a table from Access into Oracle using DTS. I let DTS to create the table in Oracle and then populate it with data. It creates the table but as soon as it tries to copy the data I get an error message: Table does not exist. Any idea why?I am trying to copy a table from Access into Oracle using DTS. I let DTS to create the table in Oracle and then populate it with data. It creates the table but as soon as it tries to copy the data I get an error message: Table does not exist. Any idea why?

More of an Oracle question really than SQL. I'm not the expert on Oracle, but I would gues that it has to do with schema ownership. Try qualifying the name of the table in the DTS package with the owner (usually the user id underwhich the table was created).

Regards,

hmscott

Dts

I have 3 separate DTS to transfer tables.
They transfer tables from 3 different Access databases
into one SQL server database.
But I would like to make them into one dTS so that I can
just check on error log file.
How can I do this?
Thanks for any suggestions.Basic copy-pasting of the entire contents of your DTS packages into one would do the trick because a DTS can have many separate connections and many "tasks" even if they are not related.

Now, you might want to keep them separate because if one fails (because of data transformation for example), you could still run the other two. If you keep them separate, use 'dtsrun' with the SQL Server Agent to run all your packages in different steps (and setting the "On failure" option to "Go to the next step") hence -kind of- grouping them together.

Good luck,

SC|||You can enable DTS package logging as specified by BOL:
To enable package logging

Open the Data Transformation Services (DTS) package for which you want to create a log.

On the Package menu, click Properties to display the DTS Package Properties dialog box.

Do one of the following:
Save package logs to Microsoft SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.

Security Note When possible, use Windows Authentication.

Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
... which will help you to assess the package(s) information.|||Thanks for all your responses.

Dts

Hi All,

I am trying to populate an Oracle table with the data from an Excel spreadsheet using DTS. The Excel spreadsheet has three date fields. When I run DTS one of these three fields is populated correctly and the other two just come over as blank. Any idea?

Any help is appreciated.Whenever importing data, especially from unreliable formats such as Excel, it is wise to load the records into a staging table first, and then use a stored procedure to verify, cleanse, and transfer the data to your production table.
Make the columns in your staging table NVARCHAR2, so you can see what is actually coming into them, and that will help you debug the situation.|||excel? DTS? SQL Server? oracle?

Dts

I need to run a DTS package everyday twice.
Everytime it should fetch some record and put in a excel sheet (in a definite place).Now my requirement is that this DTs should override the excel file everytime.As it is a daily requirment I need this.How can I get new fresh records everytime?Refer to this KBA [http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319951] for more information.

You can schedule the DTS package in order to execute the package twice or as per the requirement.

Dts

Hi everybody

I have created tables in oracle 9i under a user using DTS in SQL Server. (i have exported from sql server to oracle 9i using DTS) . When i query select * from tab , it list al the tables created . But when i desc the structure of table or select * from table_name ,it shows object or view does not exist. what will be the problem ?
pls reply
ThanksWell this is more of an Oracle question...but I think the answer lies in the fact of the "user" (which in sql server is like the owner) who created the table.

Plus I wouldn't do it that way...

I would create all of my objects using DDL, bcp the data out to a pipe delimited file, copy the data to the Oracle box, and then use sqlLoader to populate the table.

DTS

What is the easiest way to give a user the abilitiy to run a DTS package
without having the Enterprise Manager on their workstation?I'm no expert my any stretch...I think there's an option to save your DTS
package as a Visual Basic executable file. If I remember correctly, this
option is provided while you go through the import/export wizard.
"Johnny" wrote:

> What is the easiest way to give a user the abilitiy to run a DTS package
> without having the Enterprise Manager on their workstation?|||Hi
Does the user have QA on his/her workstation?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:5EAD6100-98E2-4378-B38D-D3BB8F059CE0@.microsoft.com...
> What is the easiest way to give a user the abilitiy to run a DTS package
> without having the Enterprise Manager on their workstation?|||> What is the easiest way to give a user the abilitiy to run a DTS package
> without having the Enterprise Manager on their workstation?
They need to have the dts runtime on their computer, then you can call
dtsrun.exe from the command line, for more info look in BOL.
If you wish to call a dts package from a sql script you can use
xp_cmdshell..

DTS

Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanitha
thanks a lot
vanithaHi
I think a multi-phase data pump may be what you are looking for check out
http://www.sqldts.com/default.aspx?282
John
"Vanitha" wrote:

> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
> thanks a lot
> vanitha|||Thanks John.
I am not getting the Phases Tab. How we will get "Visual Basic
Transformation Script"
Thanks
vanitha
"John Bell" wrote:
> Hi
> I think a multi-phase data pump may be what you are looking for check out
> http://www.sqldts.com/default.aspx?282
> John
> "Vanitha" wrote:
>|||Hi
If you have enabled viewing multiphase transaformations (see figure 1.1)
then to get you will need to delete any existing transformations created and
then create new activeX transformation. You can add phases on the phases tab
on the transformation options, or the phases tab on the Active X
transformation properties (properties button on the general tab of the
transformation options dialogue)
John
"Vanitha" wrote:
> Thanks John.
> I am not getting the Phases Tab. How we will get "Visual Basic
> Transformation Script"
> Thanks
> vanitha
> "John Bell" wrote:
>

Dts

Do you know how launch a dts package from aanother machine which is not the db server.What exactly are you trying to do ... could you explain a bit more ?|||3 ... 2 ... 1 ... Blast-off ? :D

DTS

Hi,

What is good way of executing .dtsx jobs in sql 2005, when stored in file format, or msdb database. Can my sql agent read jobs from file format as well as msdb database ?

Thanks &Regards

Nitu

Yes SQLAgent can execute file system or msdb packages. I'm surprised you could not find that information in Books Online. The online version is here ...

http://msdn2.microsoft.com/en-us/library/ms141701(SQL.90).aspx

At the bottom of every Books Online page there is an option to vote for the usefulness of the content. You can do that to help us improve the content - I'm concerned that you are not finding this information there, so any help you can give us would be appreciated.

Donald Farmer

DTS

One quick question....
If I DTS to import some data, does is trigger replication??
I imported a text file using DTS and I don't see the data in my subscriber.
Thank you in advance
Marlene
Marlene,
which task did you use to import the data? If it was the transform data
task, then unchecking the 'fast load' option should cause the triggers to
fire.
HTH,
Paul Ibison
|||Hello,
I'm using the wizard to import Data, and I can not find the option you
mention. Is there any way that I can specify this option using the
wizard??
Thank you in advance.
Marlene A. Roman
**********************
Marlene,
which task did you use to import the data? If it was the transform data
task, then unchecking the 'fast load' option should cause the triggers to
fire.
HTH,
Paul Ibison
********************
One quick question....
If I DTS to import some data, does is trigger replication??
I imported a text file using DTS and I don't see the data in my subscriber.
Thank you in advance
Marlene
|||This option is not available through the import/export date menu itmes, but is available through the DTS services.
When you create a package, right click on your transformation and select properties. Its in the Options tab and is called Use Fast Load.
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
************************************************** *********
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
************************************************** ***********
Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:

> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ************************************************** *********
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
> Package execution complete. Process Exit Code 1. The step failed.
> ************************************************** ***********

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killer
doller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegrou ps.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>
|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller
|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegr oups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

Hello,
I want to imports text file having Header and detail sections. Both Header
and Detail sections have different number of fields and different field
separators.
How can i imports such a file using DTS
Thanks
read the file twice, filter the header records in one process to a temporary
table, process it again for the details again to a temporary table. process
the temporary tables(s) again to scrub (validate the data) to where ever you
want.
Nik Marshall-Blank MCSD/MCDBA
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CA3AA9AF-5FEE-40D7-84B1-54BE55360E64@.microsoft.com...
> Hello,
> I want to imports text file having Header and detail sections. Both Header
> and Detail sections have different number of fields and different field
> separators.
> How can i imports such a file using DTS
> Thanks

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do?
Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do?
>
|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do?
>
|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI. exe
Is this the right exe file to use?
|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:

> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI. exe
> Is this the right exe file to use?
>
|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.
|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:

> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

DTS

What are you talking about?
I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
Whats the to part?

>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
Then dont try to re-create it.

>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
Of course you can. But keep in mind this is a db file. It
sounds though as you just created it so it's probably
empty. Drop the db from Enterprise Manager.
Why are you exporting to a file anyways?

>--Original Message--
>I am transfering data from a production server to a test
>sever. I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
>so I removed that db from the drop down list. Now when I
>try to do another transfer I get the following error
>message.
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
>.
>
The to part that I was refering to is on the Chose Destination page of the DTS wizard in the database drop down list. In that list it has a new option. After selecting that I typed in the name of the database that I want to create with the data transfer
. I am not trying to create the file that gives me the error I assume that this is something that is created by the DTS.

DTS

Can you script a DTS package?
Or is there another way to copy a DTS package from one server to another (i.e. from testing to production)?
Please let me know ideas.
Thanks!
Yes. When you click Save As, change the location from SQL Server to either
of the other two options... Then you can save it as a file and copy it
wherever you want. To open the file, right-click on Data Transformation
Services in EM and click Open Package...
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:C4B4662C-C87E-409B-85D3-20732A0ADB85@.microsoft.com...
> Can you script a DTS package?
> Or is there another way to copy a DTS package from one server to another
(i.e. from testing to production)?
> Please let me know ideas.
> Thanks!
|||On Fri, 14 May 2004 09:46:01 -0700, Ysandre wrote:

> Can you script a DTS package?
> Or is there another way to copy a DTS package from one server to another (i.e. from testing to production)?
> Please let me know ideas.
Just save it as a file and then import it to the production server.
Morten Isaksen
http://www.aub.dk/~misak/
|||Thanks! That's exactly what I was looking for.
I figured out the save as structured file part, but couldn't find the instructions on what to do with it after that!
Thanks again!
Have a great weekend.

DTS

Could you help me with this problem: I have a DTS
executing .bat file and when try to run it in enterprise
management error occurs "The device is not
ready." Other DTSs with SQL queries work fine.
I'd guess that DTS is trying to access a physical drive letter that
doesn't exist or isn't mapped. Use UNC paths instead of drive letters
and see if the problem goes away.
David Portas
SQL Server MVP
|||I am using the UNC path
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121875027.700950.158000@.g43g2000cwa.googlegr oups.com...
> I'd guess that DTS is trying to access a physical drive letter that
> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
> and see if the problem goes away.
> --
> David Portas
> SQL Server MVP
> --
>
|||docsql wrote:[vbcol=seagreen]
> I am using the UNC path
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> message news:1121875027.700950.158000@.g43g2000cwa.googlegr oups.com...
Are you sure the account running the DTS script has access to the UNC
you provide?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||yes
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23At6ZwUjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> docsql wrote:
> Are you sure the account running the DTS script has access to the UNC you
> provide?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Hello,
Similar issue will occur when using the DTS import/Export wizard, select
the option "Copy Objects and data between SQL server databases" and the
script file directory pointing to a wrong drive.
If this is the case, to narrow down the issue, copy the script file to the
local hard drive and recreate a new DTS package to test.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

DTS

I used the DTS to transfer data to a new server, I received an error message "Bulk copiy failure", but in checking my destination datanbase data was indeed transfered.
Is it safe to assume that there are no errors, or what is the source of the error?
Thanks
You can receive this error if there are blank lines in the end of the
file you are transferring and the destination has non-nullable columns.
You should try to eliminate blank rows from the source file, use an
ActiveX task to skip rows that are blank or bring the data into a
holding table with all nullable columns then copy the data from there to
your real tables.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

DTS

I have a question regarding DTS in SQL Server, I am trying to match
tables that are present in Databse A to Database B. I Know to match the

columns but I am not aware of how exactly can i transfer values of
tables present in databse A to tables present in database B.

Is there anyone who can help me with this.

Thanks in advance
KellyI'm not sure what you mean - what does "matching" the tables mean? The
basic way to transfer data is to create two MSSQL connection objects,
and use a Transform Data task to copy the data. Have you tried this?

If this doesn't help, I suggest you give some more information - what
version of MSSQL, what exactly you need to do, what exactly you've
tried so far etc.

Simon

Dts

Dear All,

I want to create DTS using an excel file is source and destination is DB-Table.

Please help me, how to proceed.

Thanks,
Tosyed

Quote:

Originally Posted by tosyed

Dear All,

I want to create DTS using an excel file is source and destination is DB-Table.

Please help me, how to proceed.

Thanks,
Tosyed

Hi there,

Please do not double post. You are using MSSQL or MySQL? Which one?|||

Quote:

Originally Posted by sashi

Hi there,

Please do not double post. You are using MSSQL or MySQL? Which one?

I am using MSSQL.|||Use DTS Import/Export Wizard and then save it to the SQL Server msdb database

DTS

How do we configure DTS package to transfer more than 1 table data transfer from a Source to Destination Server?
I have about 100 odd tables in Source & need to do data transfer for all within 1 go. Is this possible?If you are on SQL Server 2000 and the source and destination
are both SQL Server(7 or above), you can use the Copy SQL
Server Objects task.
-Sue
On Wed, 5 Nov 2003 13:11:07 -0800, "Prasanna"
<anonymous@.discussions.microsoft.com> wrote:
>How do we configure DTS package to transfer more than 1 table data transfer from a Source to Destination Server?
>I have about 100 odd tables in Source & need to do data transfer for all within 1 go. Is this possible?|||The simplest way is USe the DTS wizard.
or
In , SQLEM,go to "Tools" and then CLick "DTS" and then Use
the Export/Import. (This will also get you the DTS Wizard)
Very simple and user friendly wizard.You can easily do it.
SQLVarad(MCDBA-1999,MCSE-1999)
>--Original Message--
>If you are on SQL Server 2000 and the source and
destination
>are both SQL Server(7 or above), you can use the Copy SQL
>Server Objects task.
>-Sue
>On Wed, 5 Nov 2003 13:11:07 -0800, "Prasanna"
><anonymous@.discussions.microsoft.com> wrote:
>>How do we configure DTS package to transfer more than 1
table data transfer from a Source to Destination Server?
>>I have about 100 odd tables in Source & need to do data
transfer for all within 1 go. Is this possible?
>.
>

DTS

Hi,
I created a DTS that will do the following:
Does a select on a table, then exports the results to an oracle table, however when it does and insert i tell it to :
if exists: update
if not exists: insert
it=B4s not working... can anyone help me? i am using an active script do to this "if" job for me and i am using lookups.
Does anyone know what i can do? I have searched all over the net on how to export table to an oracle database but no luck... any sites?
:)At the start of the package use an ExecuteSQL task and query I believe
SYS.ALL_TABLES. You then use PLSQL to do the Equivalent of the SQL Server
IF EXISTS(..............). The ExecuteSQL task is provider specific in
syntax.
You can then simply do your datapump afterwards. To set up the datapump
though you will need a dummy table in Oracle so you can complete the
mappings in the datapump setup. You can delete it right after. It does not
need to be the same name only the same structure. You can apply the name
afterwards by using the object model and the DestinationObjectName property
of the DataPump.
Hope this helps
--
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
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:12f1601c3c0e8$5f926080$a601280a@.phx.gbl...
Hi,
I created a DTS that will do the following:
Does a select on a table, then exports the results to an
oracle table, however when it does and insert i tell it
to :
if exists: update
if not exists: insert
it´s not working... can anyone help me? i am using an
active script do to this "if" job for me and i am using
lookups.
Does anyone know what i can do? I have searched all over
the net on how to export table to an oracle database but
no luck... any sites?
:)

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:
> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:
> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

DTS

I used DTS to export data to a text file.
Then I used these text files to import to another database, during the DTS
import, why I did not need to specify the table to be imported the data ?Hi Alan
This would be a two stage process, export the table data and then the second
process would be to import the data from the text files. You would need two
tasks (or jobs) to do this, and it sounds like you have only specified the
export part! You may want to transfer the data directly from one table to the
other in your DTS job, which will be quicker. If you definately need the data
to be put to file you may want to try using the BCP utility instead (See
books online for more about this).
John
"Alan T" wrote:
> I used DTS to export data to a text file.
> Then I used these text files to import to another database, during the DTS
> import, why I did not need to specify the table to be imported the data ?
>
>

DTS

Could you help me with this problem: I have a DTS
executing .bat file and when try to run it in enterprise
management error occurs "The device is not
ready." Other DTSs with SQL queries work fine.I'd guess that DTS is trying to access a physical drive letter that
doesn't exist or isn't mapped. Use UNC paths instead of drive letters
and see if the problem goes away.
--
David Portas
SQL Server MVP
--|||I am using the UNC path
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
> I'd guess that DTS is trying to access a physical drive letter that
> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
> and see if the problem goes away.
> --
> David Portas
> SQL Server MVP
> --
>|||docsql wrote:
> I am using the UNC path
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
>> I'd guess that DTS is trying to access a physical drive letter that
>> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
>> and see if the problem goes away.
>> --
>> David Portas
>> SQL Server MVP
>> --
Are you sure the account running the DTS script has access to the UNC
you provide?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||yes
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23At6ZwUjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> docsql wrote:
>> I am using the UNC path
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
>> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
>> I'd guess that DTS is trying to access a physical drive letter that
>> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
>> and see if the problem goes away.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Are you sure the account running the DTS script has access to the UNC you
> provide?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Hello,
Similar issue will occur when using the DTS import/Export wizard, select
the option "Copy Objects and data between SQL server databases" and the
script file directory pointing to a wrong drive.
If this is the case, to narrow down the issue, copy the script file to the
local hard drive and recreate a new DTS package to test.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

DTS

Hello,
I want to imports text file having Header and detail sections. Both Header
and Detail sections have different number of fields and different field
separators.
How can i imports such a file using DTS
Thanksread the file twice, filter the header records in one process to a temporary
table, process it again for the details again to a temporary table. process
the temporary tables(s) again to scrub (validate the data) to where ever you
want.
--
Nik Marshall-Blank MCSD/MCDBA
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CA3AA9AF-5FEE-40D7-84B1-54BE55360E64@.microsoft.com...
> Hello,
> I want to imports text file having Header and detail sections. Both Header
> and Detail sections have different number of fields and different field
> separators.
> How can i imports such a file using DTS
> Thanks

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
***********************************************************
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
*************************************************************Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:
> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ***********************************************************
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
> Package execution complete. Process Exit Code 1. The step failed.
> *************************************************************

DTS

Hello Group,
I would like to know if DTS can be used to migrate data
from db2 to sqlserver2000.
Any pointers would be great!!
Thanks for the help,
Chris.See if this helps -
http://databasejournal.com/features/mssql/article.php/1444051 I would also
check sqldts.com
Ray Higdon MCSE, MCDBA, CCNA
--
"chris" <chris@.nospam.com> wrote in message
news:2QGPb.5113$ro4.79@.nwrdny02.gnilink.net...
> Hello Group,
> I would like to know if DTS can be used to migrate data
> from db2 to sqlserver2000.
> Any pointers would be great!!
> Thanks for the help,
> Chris.
>

DTS

Can you script a DTS package
Or is there another way to copy a DTS package from one server to another (i.e. from testing to production)
Please let me know ideas
Thanks!Yes. When you click Save As, change the location from SQL Server to either
of the other two options... Then you can save it as a file and copy it
wherever you want. To open the file, right-click on Data Transformation
Services in EM and click Open Package...
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:C4B4662C-C87E-409B-85D3-20732A0ADB85@.microsoft.com...
> Can you script a DTS package?
> Or is there another way to copy a DTS package from one server to another
(i.e. from testing to production)?
> Please let me know ideas.
> Thanks!|||On Fri, 14 May 2004 09:46:01 -0700, Ysandre wrote:
> Can you script a DTS package?
> Or is there another way to copy a DTS package from one server to another (i.e. from testing to production)?
> Please let me know ideas.
Just save it as a file and then import it to the production server.
--
Morten Isaksen
http://www.aub.dk/~misak/|||Thanks! That's exactly what I was looking for
I figured out the save as structured file part, but couldn't find the instructions on what to do with it after that
Thanks again
Have a great weekend.

DTS

I am transfering data from a production server to a test
sever. I started a transfer with the DTS wizard then I
realized that I had the name spelled wrong in the to part
so I removed that db from the drop down list. Now when I
try to do another transfer I get the following error
message.
Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
it alredy exists.
When I check the test server this file does exist.
What do I need to do to get my transfer to go?
Can I just delete the above mentioned file?
What is the purpose of the data folder?What are you talking about?
I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
Whats the to part?
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
Then dont try to re-create it.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
Of course you can. But keep in mind this is a db file. It
sounds though as you just created it so it's probably
empty. Drop the db from Enterprise Manager.
Why are you exporting to a file anyways?
>--Original Message--
>I am transfering data from a production server to a test
>sever. I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
>so I removed that db from the drop down list. Now when I
>try to do another transfer I get the following error
>message.
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
>.
>|||The to part that I was refering to is on the Chose Destination page of the DTS wizard in the database drop down list. In that list it has a new option. After selecting that I typed in the name of the database that I want to create with the data transfer. I am not trying to create the file that gives me the error I assume that this is something that is created by the DTS.

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killerdoller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegroups.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

Does anyone know a good site, or have any information
about creating a DTS that will import tables from SQL 2000
to Oracle'
ThanxsNot sure but try www.sqldts.com
>--Original Message--
>Does anyone know a good site, or have any information
>about creating a DTS that will import tables from SQL
2000
>to Oracle'
>Thanxs
>.
>|||Yes, www.sqldts.com, sponsored by one of our MVPs Darren Green in the UK
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:011801c3c009$0e619260$a301280a@.phx.gbl...
> Does anyone know a good site, or have any information
> about creating a DTS that will import tables from SQL 2000
> to Oracle'
> Thanxs