dtsrun from stored procedure

Hi all,

I am currently using a series of dts packages to extract and export data. To run the packages, I have a table (TW_DTS) which lists each dts package name, I then use a stored procedure to loop through each and 'dtsrun' it. If a package fails then the error is stored in another table from within the individual dts.

This solution works in principle, however when the stored procedure runs some of the dts packages fail for an unknown reason. Unfortunately the problem is not consistent - it is not always the same package, or even same number of packages that fail. Each package will work when run individually.

I have listed the stored procedure below, any advice may save me from throwing my laptop through the window.

Cheers.

----

CREATE PROCEDURE [spTWDTS] AS

DECLARE @.unique_id varchar(50)
DECLARE @.dts_name varchar(50)
DECLARE @.start_dttm datetime
DECLARE @.end_dttm datetime
DECLARE @.CMD varchar(1000)
DECLARE @.ERROR varchar(1000)

DECLARE
dts_cur CURSOR FOR SELECT unique_id, dts_name, start_dttm, end_dttm from TW_DTS where status = 'A'

open dts_cur

FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

while @.@.fetch_status = 0

BEGIN
-- Set as No Error
SET @.ERROR = 0

--update the start date
UPDATE TW_DTS set start_dttm = getdate() where unique_id = @.unique_id

--run the package
SET @.CMD = 'dtsrun /S myserver /U myusername /P mypassword/N '+@.dts_name
EXECUTE @.ERROR = master..xp_cmdshell @.CMD

--update the end date
UPDATE TW_DTS set end_dttm = getdate() where unique_id = @.unique_id

--move to next dts package
FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

END

CLOSE dts_cur
DEALLOCATE dts_cur

RETURN @.ERROR
GONot sure about this but perhaps dtsrun /L might help in finding out where/when the execution fails. You might want to add a select 'executed: ' + @.dts_name, 'Error', @.error after the xp_cmdshell. I assume there's a space between the password and the /N ? And perhaps adding a 'start /w ' to the command might help.

Like I said, I'm not sure if their any help but it might get you on the right track.|||Thanks Kaiowas,

Tried that and got the following error message in the log file:

Step 'Copy Data from Results to [export].[dbo].[extract] Step' failed

Step Error Source: Microsoft OLE DB Provider for Oracle
Step Error Description:Oracle error occurred, but error message could not be retrieved from Oracle.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:

The step just runs an oracle query and put the results into the 'extract' table.

Not really any the wiser now, do you know how I can get to the oracle error?|||It's an MDAC error message, so Oracle won't help you there. I find google to be very helpful in such cases, it came up with: http://support.microsoft.com/kb/255084/EN-US/

DTS/SSIS too many tables warning and subsequent error

I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.

First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:

"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"

Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.

This was never an issue with SQL 2000 DTS.

Thanks

"a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

The wizard told you that it may not be able to do it.

|||

Hey,

Hah! Nice man. Well I hear you on that one, but why is this an issue with the SQL 2005 DTS and not the older SQL 2000 DTS, and is there any way around it?

Thanks

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.
Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>
|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napsal v sprve
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napsal v sprve
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napísal v správe
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
>> hour. I created DTS (Data Transformation Services) packages by wizard.
>> It works fine first time but when DTS runs next time it ends with error:
>> table 'xxxx' already exist. How to manage the overwrite of this Excel
>> Sheet 'xxxx' every hour automatically?
>>
>> Thanks,
>> Martin.
>>
>

DTS Variables..?

Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?I tend to stay away from DTS... Some smartly written stored procs using Bulk Insert should do the trick...|||Yes,

I've done it from a stored procedure. Not sure how to go from ado.net directly to DTS.

Here a couple links to examples of using global variables. Not exactly what you need, but a start. It's what I used to get going and figured it out from there.

http://www.swynk.com/friends/green/textfile.asp
http://www.swynk.com/friends/green/DTSHowTo3.asp

http://www.sqldts.com/
http://www.dts2000.com/|||i did it this way from VB.NET...

Dim DTSPackage As New DTS.Package2
DTSPackage.LoadFromSQLServer(Config.Server, Config.User, Config.Pwd, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", DTSPackageName)
DTSPackage.GlobalVariables().Remove("ImportFileName")
DTSPackage.GlobalVariables().AddGlobalVariable("ImportFileName", ImportFileName)
DTSPackage.GlobalVariables().Remove("ExportCatalog")
DTSPackage.GlobalVariables().AddGlobalVariable("ExportCatalog", ExportCatalog)

i had a little trouble resetting the value of the global var in the DTS package if it already existed so i just removed it and re-added it. I'm sure theres a better way but...time was scarce.

Note: DTS Package Global Vars are case sensitive|||Thank you,

Got it late, but still works good!|||I'm having one little problem with this whole thread. What class library is the DTS.Package2 contained in? I can find no reference to it anywhere!|||DTS is a COM library that gets installed when you load SQL server
its named: dtspkg.dll
its titled: Microsoft DTSPackage Object Library
it lives in SQL Servers BINN folder|||Thanks a bunch. That's exactly what I needed. I've always been more on the SQL side than the .NET side. I searched everywhere for that reference and couldn't find a single one for DTS. Since it is only executed once a month (and never on the same day) I couldn't schedule it as I normally would.

DTS Truncating field from 8000 to 255

hi,
I am using DTS to export to a text file and i have a field that is a
VARCHAR(8000) field. It is being truncated to 255. When I look at the
destination tab in DTS, it says that the size is 8000. Any ideas?
please advise.
rafaelhow do you know it is being truncated?. Are you doing a query using Query
Analyzer?, if so, then you have to change the value of the option Tools -
Options... - Results - "Maximum characters per column", the default is 256
and the max value is 8k or 8192.
AMB
"Rafael Chemtob" wrote:

> hi,
> I am using DTS to export to a text file and i have a field that is a
> VARCHAR(8000) field. It is being truncated to 255. When I look at the
> destination tab in DTS, it says that the size is 8000. Any ideas?
> please advise.
> rafael
>
>|||Hi,
Looks like you got problems while retrieval. If it is a problem with
retrieval then use the below commands:-
SET TEXTSIZE { number }
select @.@.textsize
See books online for more details.
Thanks
Hari
SQL Server MVP
"Rafael Chemtob" wrote:

> hi,
> I am using DTS to export to a text file and i have a field that is a
> VARCHAR(8000) field. It is being truncated to 255. When I look at the
> destination tab in DTS, it says that the size is 8000. Any ideas?
> please advise.
> rafael
>
>

DTS to truncate excel and load fresh data

I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve thisCan you try again?

I'm not sure I follow you...

Can you put the steps you do down in bullets...and what the final; result would be

1. I DTS in to a table
2. ect

I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.

'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********

Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1

' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")

If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If

If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If

' ****************
' Create new Excel Workbook
' **************

' Create object
Set xlApp = CreateObject("Excel.Application")

' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add

' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"

' ****************
' Format new Excel Workbook
' **************
With wkbNewBook

.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With

Set wkbNewBook = Nothing
Set xlApp = Nothing

End Function

##############################33

Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this

DTS to Lotus notes

DTS to Lotus notes

I must import/export some data from SQL Server to Lotus Notes.
I downloaded and installed NotesSQL ODBC drivers.
I met no problem importing from a Lotus Notes view to an SQL Server table: I used DTS and everything succeded.
But I can't do the reverse process (exporting from SQL Server to Lotus Notes): DTS keeps on giving an error without description and it fails.
Does anybody know how to export a table or a view from SQL Server to Lotus Notes using DTS?

Thanks in advance.Are you trying to setup a daily process, or is it a 1 time thing?

Do you have Notespump or Notestrix?

And as much as they protest, Lotus Notes is NOT a database...

Sorry...I got carried away...

And what do you mean...are you trying 2 way replication?|||Originally posted by Brett Kaiser
Are you trying to setup a daily process, or is it a 1 time thing?

Do you have Notespump or Notestrix?

And as much as they protest, Lotus Notes is NOT a database...

Sorry...I got carried away...

And what do you mean...are you trying 2 way replication?

Thanks for the reply. This would be a weekly process that I would like to setup and schedule via DTS. I would agree that notes is not a database by definition. I would just like to automate this if possible. It is a very small dataset based on an SQL query. Do I have a bad version of NotesSQL that only works one way?! I don't have Notespump or Notestrix. Wouldn't those apps be a bit overkill? Thanks! - JB|||I haven't done this...but I'm going to try it...got a link for the driver?

Also, how do you authenticate?

Maybe it's a permissions issue...|||Originally posted by Brett Kaiser
I haven't done this...but I'm going to try it...got a link for the driver?

Also, how do you authenticate?

Maybe it's a permissions issue...

The site is: www.lotus.com/notessql

The owner of the DB has given me read/write
permission on the DB. Each time DTS attempts
to read or write to the .nsf, I am prompted for
my lotus notes password and it is accepted.|||Still looking

Found this

http://www.lotus.com/products/rnext.nsf/873769A79D9C5B2285256A0800720B96/F8BA31E9F7BDEC3A85256C4700667625?OpenDocument

Does it have to be installed on the server, or can it be installed on the client?

I guess where ever DTS is running, no?|||Originally posted by Brett Kaiser
Still looking

Found this

http://www.lotus.com/products/rnext.nsf/873769A79D9C5B2285256A0800720B96/F8BA31E9F7BDEC3A85256C4700667625?OpenDocument

Does it have to be installed on the server, or can it be installed on the client?

I guess where ever DTS is running, no?

Yes. I think you would want to install it on the server. You will then be permitted to setup an odbc connection there to an nsf file.|||You can install on the client...just did it...

Bam shows up in my source selection list...

Now I gotta find a notes server to link to...

Did you see the user manual after the install?|||I've reviewed the "help" html doc a bit. It seems to talk more about extracting data to another DB.|||The site is: www.lotus.com/notessql

...Each time DTS attempts to read or write to the .nsf, I am prompted for
my lotus notes password and it is accepted.

Don't want this to happen every time? Go into your registry under HKLM->Software->ODBC->ODBC.INI, then find the entry for your connection. Add a new string called SuppressUID and set it to 1. You won't get prompted anymore. You do have to do this for all of your Notes connections though.

DTS to export formated date to excel

Hello,

I am trying to output data from my sql table to an excel spreadsheet and send it by email which works fine, the problem is he wants the date to be in the format d-mmm-yy, which is easy to format in excel manually, but he do not want to do this manually. I tried to do this when I select the date from the table to spreadsheet, "select convert(char,value_date,106) from table", but this don't get transported to the excel spreadsheet, I get my results on the spread sheet as dd/mm/yy. Can you please help either to set the date on excel forever to be in this format "d-mmm-yy" or to force this output to excelOk, i manage to answer myself, you need to format the cells onto the excel file itself

DTS to Excel Export - Changing Format

Hi,

I have a dts package which is reading from a sql table and writing it to an excel file, its working fine except that I have a decimal field in the sql table but in excel file its writing it as string field.

The way I create this package is that I create a template file and I format that column to a "Number" format. Then I take this template file, rename it, export all the data to this file.

But when I open this file that decimal field is displayed as a string column and its left aligned.

Is there any way to fix this problem?

Thanks,I've never seen this happen. You aren't wrapping you values in quotes are you when you export them.|||No, I am not wrapping values in quotes.

DTS to do SQL to MS Access

Okay guys, maybe I'm making this much more complex than it needs to be. I've got a SQL DB and want to export certain tables to Access. My first thought was DTS, right? So I started down the road of the DTS - build the DTS on my local SQL box, made the Access DB, and everything was good.

Then I thought about my deployment environment: there are several clients that will use this. Some will have their own environment, so no problem. Yet others will host at an ISP, which typically means that the SQL box only has TCP/IP access to the webserver, so where do you tell the DTS package to put the Access DB? Then I thought about using the FTP task, but this blasted thing only gives you a mapped network location to put the resulting file. DAMN! (Now why wouldn't you have the ability to put an FTP ADDRESS AS THE DESTINATION OF THE FREAKING FTP TASK - I suppose that would make too much f-ing sense!)

So here I am, at a loss for what to do. I thought about coding the transformation in .NET, but what a major pain in the ****! Then maybe thought about using DTS from within C#, but can't find any solid examples of doing this.

HELP!!!Hi Rob

Maybe this will get you on the right track. http://www.sqlteam.com/item.asp?ItemID=12408

If not, there are a couple of other FTP and DTS articles on SQLTeam|||Okay, I've worked up a little C# routine to actually execute the DTS packages and it gets so far, but then get an error that it doesn't like the userID because it's null (although I am passing the userID into the DTS execution). I've got to be missing something pretty simple here - any ideas?

Here's the C# code that calls the DTS package:

DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer("sqlmachinenamehere", "sa", "passwordhere", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection.DTSSQLStgFlag_Default, null, null, null, "Export_Association_Table", ref pVarPersistStgOfHost);

And the error that is produced:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.