DTS Question

I have a DTS job that exports into a tab-delimited file. The tab delimiting
is working great BUT it also using quotes around the fields as well. (I
assume because they are string vars). Can this quoting "feature" be turned
off?

ThanksThank you, Ray. That did it!
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:3f280abb$0$197$75868355@.news.frii.net...
> Yes, open the connection to your text file in dts designer and go to
> properties, there you will be able to specify if you want double-quotes
> as a text qualifier,row-delimiters, column delimiters, etc.
> HTH
> Ray Higdon MCSE, MCDBA, CCNA
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

DTS Programming. Reference to TransferLoginsTask

Hello,
I'm working on a C# application that uses the DTS object library via COM
interop. Until now it has been working nicely. Now I would like to get a
reference to what it seems a special task category: TransferLogin,
TranferJobs, etc.
The CustomTaskId I'm getting from those tasks are
OMWCustomTasks.OMWTransferLogins and similar. I can't find the definitions
for those objects. I'm referencing in my project custtask.DLL and dtspkg.DLL
.
Any info on this would be appreciated.
Regards
JoseHi Jos,
How odd, from my front-end app (Vb 6) I can see perfectly this task
instantiating dtspkg.dll
"DTSTask_OMWCustomTasks.OMWTransferLogins_1"
This task own three properties: All logins, Description, Name
Current location: Alicante (ES)
"Jos G" wrote:

> Hello,
> I'm working on a C# application that uses the DTS object library via COM
> interop. Until now it has been working nicely. Now I would like to get a
> reference to what it seems a special task category: TransferLogin,
> TranferJobs, etc.
> The CustomTaskId I'm getting from those tasks are
> OMWCustomTasks.OMWTransferLogins and similar. I can't find the definitions
> for those objects. I'm referencing in my project custtask.DLL and dtspkg.D
LL.
> Any info on this would be appreciated.
> Regards
> Jose
>|||Hi Enric,
I can get the task through PackageClass.Tasks collection too.
What I would like to do is to get a typed reference to that task. This is
what I'm doing with other kind of tasks:
DTS.SendMailTask myTask = (DTS.SendMailTask)task.CustomTask;
This way I should be able to get not only the AllLogins property but the
source and destination servers info.
"Enric" wrote:
> Hi Jos,
> How odd, from my front-end app (Vb 6) I can see perfectly this task
> instantiating dtspkg.dll
> "DTSTask_OMWCustomTasks.OMWTransferLogins_1"
> This task own three properties: All logins, Description, Name
> --
> Current location: Alicante (ES)
>
> "Jos G" wrote:
>|||Ok, I've found the library I was looking for: CDWTasks.dll
It contains the interface definitions. Browsing the properties of
OMWTransferLogins is not enough to get, for example, connection info. It is
retrieved calling a function.
Anyway thanks for your help.
Regards.
Jose
"Enric" <vtam13@.terra.es.(donotspam)> escribi en el mensaje
news:9FA95B0E-57CF-4EDC-B9B6-7F515A147331@.microsoft.com...
> Hi Jos,
> How odd, from my front-end app (Vb 6) I can see perfectly this task
> instantiating dtspkg.dll
> "DTSTask_OMWCustomTasks.OMWTransferLogins_1"
> This task own three properties: All logins, Description, Name
> --
> Current location: Alicante (ES)
>
> "Jos G" wrote:
>sqlsql

Dts Problem!

Hello Everyone,

I have a DTS package, which has been working fine for few years. it runs once a month to get information from different databases and save it into the destination database. Now I get this error:

error source: microsoft ole db provider for sql server
error description: could not get the data of the row from the ole db provider 'MSOLAP'.
Conversion failed because the data value overflowed the data type used by the provider.
OLE DB error trace[OLE/DB provider 'MSOLAP' IRowset::GetData returned 0x40eda: Data status returned from the
provider:[COLUMN_NAME=[Measures].[The profit]STATUS=DBSTATUS_EDATAOVERFLOW], [COLUMN_NAME=[Measure].[Hours T2]

Any help will be apreciated!Your source table has data that is not acceptable on your side.
It could be just for example if you have int column and source table has bigint. All this time all numbers where easily converted into int datatype on your server but now one of the numbers is too big for your table and you get overflaw error .
Basically your message says that datatype on the source table cant be accepted on destination. Try to load data into a work table where all columns are character datatype and view what is not convertible into datatypes on your table.

Unfortunately solution involves some research on your part.

Good Luck.|||Thank you for your reply,

The problem is I know that the destination table has numeric datatype but the data comes from a cube on an OLAP linked server, I looked att som values there som was numerics and others '-1,#j' probably a formuler results. and I do this convert(float, "[Measures].[ The profit]"). What else do I need to do?|||As I already said.
Load data into a work table where this column is a character datatype and add additional step to your DTS package where you filter out unwanted records while loading data into production table. This way you can have exception table where you will enter invalid records and see what could be done to fix them.

Good Luck.

DTS problem

Hi,
I am trying to export data into EXCEL sheet using DTS, It is working fine
for single user, however it fails with error messages when tried by multiple
users at the same time...So is it limitation of DTS as it does not run
simultaneously ?Is the destination Excel Workbook the same for all users?
If it is, then that's why the DTS package fails. If that's not the case,
please post the actual error message.
ML|||Hi ML,
It is the same excel but user can change the path and each one is exporting
it in different folders, so I feel it should not be the case.
Errors is shown like :
"Microsoft Database Engine canot open the file ", It is already opened
exculisevely by another user, or you need permission to view data.
Is this because every user is using single user(user name + pwd, is provided
thru config file), with special permission, to fetch data from different
tables.
Thanks
"ML" wrote:

> Is the destination Excel Workbook the same for all users?
> If it is, then that's why the DTS package fails. If that's not the case,
> please post the actual error message.
>
> ML|||Judging by the error message all users are trying to access the same Excel
file.
You say they have the option of changing the output path - at what point in
time are they allowed to change it? Before or after executing the DTS packag
e?
ML|||It is through input box, which is displayed through VB script and that is th
e
part of DTS package. So It happens during the execution of DTS package.
"ML" wrote:

> Judging by the error message all users are trying to access the same Excel
> file.
> You say they have the option of changing the output path - at what point i
n
> time are they allowed to change it? Before or after executing the DTS pack
age?
>
> ML|||If the error occurs before this VB script is executed, then the connection t
o
the Excel file is made before the user selects the output file - which means
the connection is made to the same file for each user.
However, if the error occurs after the user has selected a different output
file, then it seems that the connection is not made to the file the user
selected.
Maybe you could create a new file for each user at the beginning of the
package, and establish a connection dynamically:
1) open a new file using VBS;
2) name the file by embedding the user's name and a timestamp (e.g.
"Output_John_200510301255.xls");
3) passing the filename as a global variable to the data connection.
ML

DTS Packages are not working as job

Hello,

I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).

dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.

I don't use a file as a data source or for something else in the package.

Server is SQL 2000 with SP3 and operating system is win2k.

I hope there is someone who has an idea...

elifElif:

What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?

RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.

If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?

It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:

I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.

I may be offbase but that is what he found.

Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,

I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.

bye

elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)

DTS package problems

I am working on a DTSPackage and have had nothing but bad luck.

1) the query is below. I want to sub out @.GUID with a GlobalGUID variable I set using

dynamic property. All I get when I try to set

GUID = ?

instead of GUID = @.GUID

when I hit the Parameters button all I get is this error

MicrosoftOLE DBProvider for SQL Server

Error Description:Syntax error or access violation

What can be wrong here? If I go the package properties I can

see the GlobalVariable I created.

Thanks for any leads.

Bob

>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE @.CurrentPeriod CHAR(4)
DECLARE @.GUID VARCHAR(50)

exec usp_Get_Period_UserPeriod @.CurrentPeriod OUTPUT

SELECT ProductCode AS [Code], ProductName AS [Short], ProductDescription AS [Long],
case CHARINDEX(',',SORT,1) when 1 then space(10) else LEFT(SORT,CHARINDEX(',',SORT,1)-1) end+SPACE(10) AS [Sort],
case when CHARINDEX(',,',SORT)>0 then space(10) else
SUBSTRING(SORT,CHARINDEX(',',SORT,1)+1,
CHARINDEX(',',SORT,CHARINDEX(',',SORT,1)+1)-CHARINDEX(',',SORT,1)-1) end +SPACE(10) AS [SortALN],
case RIGHT(SORT,1) when ',' then space(10) else
REVERSE(case CHARINDEX(',',REVERSE(SORT),1) when 1 then space(2) else LEFT(REVERSE(SORT),CHARINDEX(',',REVERSE(SORT),1)-1) end) end +SPACE(10) AS [SortALI]
FROM PRODUCTMASTER WHERE ProductCode IN
(SELECT PRODUCTCODE FROM DRPRODUCT WHERE GUID = @.GUID) AND PERIOD = @.CurrentPeriod ORDER BY SORT

Try the link below for a work around. Hope this helps.

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

DTS Package Help

Hi,

I've been viewing this forum for some good time but have rarely posted.

Right now, I'm working on this one dts package trying to find the table/query/view that the information is being pulled from as designed by a predecessor of mine. The DTS package in question is responsible for outputting the file csv file only has two objects in the design view of the package.
- Connection object pointing towards a connection with no descriptive name since this dates back before there was any sort of naming convention implemented in this organization.
- This connection object then has a arrow pointing toward a file output object which outputs to a file.

So basically at this point, I do not know anywhere else to go to further on this dts package and modify the source or query that is loading up this text file.

What can I do to find the source? If there are further details needed, I can and will post a picture for reference.Right-click any of the icons and select Properties.|||In the properties view of the objects, it doesn't seem to show or point toward a table/query/view.

In the connections object, it simply provides this information:
Name: Connection 1
x Existing Connection: Connection 1
Datasource: Microsoft OLE DB Provider for SQL Server
Server: (Local)
Use Windows Authentication
Database XXXX

In the File Output object, it simply provides this information:
Name: Connection 2
Existing Connection: Connection 2
Datasource: Text File (Destination)
File Name: C:\XXXX.csv

Those are the only details the properties view of the objects provides.

Edit: Found the table that it is being read from. However, I haven't found how the table is being populated.
Edit2: Ah the table was actually a view. Thanks for all your help. You pointed me in the right direction.|||Is there an arrow between the icons? Rightclick it and check the properties.

DTS Package- Check its useability

I have a quick question, I am working on some new SQL Servers that
were being managed by other DBA's. I am going through a list of DTS
packages and there are some that I am not certain if they are being
used anymore. Is there a way in SQL Server to identify how often the
DTS packages are being executed so that I can tell if it is being
used?
Any help in this regard will be greatly appreciated.
Thanks
If logging is already enabled, right click the package and select Package
Logs.
If logging is not enabled you can enable it. Open the package, go to Package
in the menu, Properties, Logging tab and Logging section.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"shub" wrote:

> I have a quick question, I am working on some new SQL Servers that
> were being managed by other DBA's. I am going through a list of DTS
> packages and there are some that I am not certain if they are being
> used anymore. Is there a way in SQL Server to identify how often the
> DTS packages are being executed so that I can tell if it is being
> used?
> Any help in this regard will be greatly appreciated.
> Thanks
>

DTS Package- Check its useability

I have a quick question, I am working on some new SQL Servers that
were being managed by other DBA's. I am going through a list of DTS
packages and there are some that I am not certain if they are being
used anymore. Is there a way in SQL Server to identify how often the
DTS packages are being executed so that I can tell if it is being
used?
Any help in this regard will be greatly appreciated.
ThanksIf logging is already enabled, right click the package and select Package
Logs.
If logging is not enabled you can enable it. Open the package, go to Package
in the menu, Properties, Logging tab and Logging section.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"shub" wrote:
> I have a quick question, I am working on some new SQL Servers that
> were being managed by other DBA's. I am going through a list of DTS
> packages and there are some that I am not certain if they are being
> used anymore. Is there a way in SQL Server to identify how often the
> DTS packages are being executed so that I can tell if it is being
> used?
> Any help in this regard will be greatly appreciated.
> Thanks
>

DTS Package- Check its useability

I have a quick question, I am working on some new SQL Servers that
were being managed by other DBA's. I am going through a list of DTS
packages and there are some that I am not certain if they are being
used anymore. Is there a way in SQL Server to identify how often the
DTS packages are being executed so that I can tell if it is being
used?
Any help in this regard will be greatly appreciated.
ThanksIf logging is already enabled, right click the package and select Package
Logs.
If logging is not enabled you can enable it. Open the package, go to Package
in the menu, Properties, Logging tab and Logging section.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"shub" wrote:

> I have a quick question, I am working on some new SQL Servers that
> were being managed by other DBA's. I am going through a list of DTS
> packages and there are some that I am not certain if they are being
> used anymore. Is there a way in SQL Server to identify how often the
> DTS packages are being executed so that I can tell if it is being
> used?
> Any help in this regard will be greatly appreciated.
> Thanks
>

DTS Job Failed

Hi All,

Sql server7.

I have scheduled a job that copies data from MS Access and stores in Sql server 7. All these months it was working fine, now i am getting the below error.
Please help.

... DTSRun: Executing... DTSRun OnStart: Copy Data from AuthBatchReport to [PCCW].[dbo].[AuthBatchReport] Step DTSRun OnError: Copy Data from AuthBatchReport to [PCCW].[dbo].[AuthBatchReport] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error: -2147467259 (80004005); Provider Error: -66913278 (FC02FC02) Error string: Disk or network error. Error source: Microsoft JET Database Engine Help file: Help context: 5003043 DTSRun OnFinish: Copy Data from AuthBatchReport to [PCCW].[dbo].[AuthBatchReport] Step DTSRun OnS... Process Exit Code 12. The step failed

TIA
AdilSounds like a hardware issue, or your disk is full, or network permissions have changed.

DTS imput oke, but the job failed

Who can help me.
I'm working with SQL2000 server
and i'm using DTS import.
I have made a schedule package (job) from a DTS.
When i run the DTS manual there is no problem, it works
But when i run the job, it failed.
But some other jobs to import a DTS there is no problem.
Who can help me.Jaap,
Look at this KB: http://support.microsoft.com/?kbid=269074
--
Regards,
Rodrigo Fernandes
"Jaap" wrote:
> Who can help me.
> I'm working with SQL2000 server
> and i'm using DTS import.
> I have made a schedule package (job) from a DTS.
> When i run the DTS manual there is no problem, it works
> But when i run the job, it failed.
> But some other jobs to import a DTS there is no problem.
> Who can help me.

DTS imput oke, but the job failed

Who can help me.
I'm working with SQL2000 server
and i'm using DTS import.
I have made a schedule package (job) from a DTS.
When i run the DTS manual there is no problem, it works
But when i run the job, it failed.
But some other jobs to import a DTS there is no problem.
Who can help me.
Jaap,
Look at this KB: http://support.microsoft.com/?kbid=269074
Regards,
Rodrigo Fernandes
"Jaap" wrote:

> Who can help me.
> I'm working with SQL2000 server
> and i'm using DTS import.
> I have made a schedule package (job) from a DTS.
> When i run the DTS manual there is no problem, it works
> But when i run the job, it failed.
> But some other jobs to import a DTS there is no problem.
> Who can help me.

DTS imput oke, but the job failed

Who can help me.
I'm working with SQL2000 server
and i'm using DTS import.
I have made a schedule package (job) from a DTS.
When i run the DTS manual there is no problem, it works
But when i run the job, it failed.
But some other jobs to import a DTS there is no problem.
Who can help me.Jaap,
Look at this KB: http://support.microsoft.com/?kbid=269074
Regards,
Rodrigo Fernandes
"Jaap" wrote:

> Who can help me.
> I'm working with SQL2000 server
> and i'm using DTS import.
> I have made a schedule package (job) from a DTS.
> When i run the DTS manual there is no problem, it works
> But when i run the job, it failed.
> But some other jobs to import a DTS there is no problem.
> Who can help me.

DTS Import not working after SP3a upgrade

I have a DTS package that is importing a fixed length file.
After installing SP3a (on PERSONAL & DEVELOPER edition) it will not
allow me to assign more than 125 fixed length column positions.
After hitting the Finish & OK button it just wipes out the column
settings.

I've reinstalled both Personal & Developer edition. DTS will allow
more than 125 prior to the service pack.You may be seeing a side effect of installing SP3 and the problem seen by
others after aplying this SP is here. There is a fix

814113 - FIX DTS Designer May Generate an Access Violation After You
Install SQL Server 2000 SP3
http://support.microsoft.com/defaul...KB;EN-US;814113

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"shinobi332" <darinmiller@.earthlink.net> wrote in message
news:afdc1036.0406011922.2359a387@.posting.google.c om...
> I have a DTS package that is importing a fixed length file.
> After installing SP3a (on PERSONAL & DEVELOPER edition) it will not
> allow me to assign more than 125 fixed length column positions.
> After hitting the Finish & OK button it just wipes out the column
> settings.
> I've reinstalled both Personal & Developer edition. DTS will allow
> more than 125 prior to the service pack.|||That worked perfectly.
Thanks Allan!!!

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<40bd7127$0$4589$db0fefd9@.news.zen.co.uk>...
> You may be seeing a side effect of installing SP3 and the problem seen by
> others after aplying this SP is here. There is a fix
> 814113 - FIX DTS Designer May Generate an Access Violation After You
> Install SQL Server 2000 SP3
> http://support.microsoft.com/defaul...KB;EN-US;814113
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "shinobi332" <darinmiller@.earthlink.net> wrote in message
> news:afdc1036.0406011922.2359a387@.posting.google.c om...
> > I have a DTS package that is importing a fixed length file.
> > After installing SP3a (on PERSONAL & DEVELOPER edition) it will not
> > allow me to assign more than 125 fixed length column positions.
> > After hitting the Finish & OK button it just wipes out the column
> > settings.
> > I've reinstalled both Personal & Developer edition. DTS will allow
> > more than 125 prior to the service pack.

DTS import not working

Hello, I have created a dts structured storage file that I want to execute with a stored procedure. The dts runs fine while in enterprise manager, but when I call the file from the stored procedure below I get this error: error LoadFromStorageFile
anybody have some ideas on what's going on?
Here is the SPROC:
DECLARE @.object int
DECLARE @.hr int

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

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, 'C:\importData.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END
GO

I'm not sure why it is failing but why not make it a scheduled job in Enterprise Manager?