DTS: No steps have been defined for the transformation package.

Hi,

I'm trying to run a few DTS packages in SQL Server 2000 using ASP and I'm getting the error below.

"Microsoft Data Transformation Services (DTS) Package (0x800403ED)
No steps have been defined for the transformation package."

Does anyone know all the possible reasons why this error occurs?

I've read that stop and starting the SQL service will do the trick but I've had no luck with that. I've also read that it could be a problem with disk space but I doubt that as I have more than 1GB available.

Oh...one thing I havent mentioned is that when I was doing a prototype for this...I created a new project with Visual Interdev and did all the ASP programming there to execute the package and that works fine but when I tried to incorporate the code into a different project...then that's when I get the error message above.

If anyone could help me out...that would be greatly appreciated!

Thanks.Would you please provide your code and mark which line your code is failing on ? Have you tried to access the properties of the package in your asp code as a debug measure ?|||It fails on when it calls the Execute method. All the other properties can be accessed and work.

I found out what was causing the problem and it's basically got to do with a setting in IIS (I'm using IIS 5.0). I'm not sure if this is a problem that is specific to the system I'm running here but when executing a DTS package from ASP...you have to make sure that the 'Application Protection' setting in IIS is set to 'Low (IIS Process)'. It will NOT work with any other setting.

Hope this helps.

DTS.Pipeline Information - Can I Access this?

Is there any way I can capture the below information? I want to capture this to get the no of rows processed by each transformation.

[DTS.Pipeline] Information: "component "abc" (3798)" wrote 2142 rows.
[DTS.Pipeline] Information: "component "xyz" (4223)" wrote 1026 rows.
[DTS.Pipeline] Information: "component "abc2" (4324)" wrote 7875 rows.

Thanks

Jamie, as usual has you covered:
http://blogs.conchango.com/jamiethomson/archive/2007/07/03/SSIS-Nugget_3A00_-Output-the-number-of-processed-rows.aspx|||

Phil Brammer wrote:

Jamie, as usual has you covered:
http://blogs.conchango.com/jamiethomson/archive/2007/07/03/SSIS-Nugget_3A00_-Output-the-number-of-processed-rows.aspx

Thanks Phil.|||This works if the transformation is in Script task, or rather if we want to generate our rows processed message in script or custom task. I dont think this can be used to access the information messages for a task.

Thanks
|||

This is what I meant to paste:

http://blogs.conchango.com/jamiethomson/archive/2007/03/08/SSIS_3A00_-OnPipelineRowsSent.aspx

Why not just use a row count transformation though?

|||

Phil Brammer wrote:

Why not just use a row count transformation though?

I already have a row count transformation, I'm just trying to see if I can knock-off the additional transformation.

Thanks
|||

Karunakaran wrote:

Phil Brammer wrote:

Why not just use a row count transformation though?

I already have a row count transformation, I'm just trying to see if I can knock-off the additional transformation.

Thanks

I don't understand. That's what it's for though. It's not really additional.

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, driver ODBC for Btrieve

Hi,
I need transformation data from files Btrieve (.dt) to any other formats. Where i can download driver ODBC or OLE DB for Btrieve?
And how install this driver?
Thank.See the vendors list (http://www.sqlsummit.com/oledbVen.htm) and get it.|||Originally posted by Satya
See the vendors list (http://www.sqlsummit.com/oledbVen.htm) and get it.
You have already used this Vendors list?
Exist of the shareware or freeware drivers?|||I've used Data Direct Technologies and for the shareware ensure to test them before deploying on production server.

Freeware/shareware are easy to get but make sure there is no security threat to the server.|||Originally posted by Satya
I've used Data Direct Technologies and for the shareware ensure to test them before deploying on production server.

Freeware/shareware are easy to get but make sure there is no security threat to the server.
What to do Data Direct Technologies? This is alternative DTS?
Give me links for downloads Data Direct Technologies, pls.|||I think you're not interested in the referred link, where this http://www.datadirect-technologies.com/ link is listed there.

Originally posted by Cancer
What to do Data Direct Technologies? This is alternative DTS?
Give me links for downloads Data Direct Technologies, pls.|||I looked only those links, which support Btrieve.
I have not found still shareware/freeware driver.

Thank for discussion|||Then may contact vendors listed, for further information.

DTS w/ Oracle Issue

Im having an issue with DTS and I dont know if anyone else has
experienced this...In my transformation I have a sql statement that
executes against oracle..in it I'm selecting COUNT(DISTINCT
I.PX_INVOICE_ID) ...however when the DTS package executes I get an
error stating that "DISTINCTI"."PX_INVOICE_ID" is an invalid
identifier..why is it concatenting this?

Thanks

JimJimbo wrote:

Quote:

Originally Posted by

Im having an issue with DTS and I dont know if anyone else has
experienced this...In my transformation I have a sql statement that
executes against oracle..in it I'm selecting COUNT(DISTINCT
I.PX_INVOICE_ID) ...however when the DTS package executes I get an
error stating that "DISTINCTI"."PX_INVOICE_ID" is an invalid
identifier..why is it concatenting this?
>
Thanks
>
Jim

It isn't Oracle ... so what version, what connection driver, etc?

But do you really have a schema named "I"? Seems strange.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||I agree with Daniel that this is most likely a client driver issue. I
haven't run into the problem myself but I've only run only simple SQL
statements using the Oracle drives. Can you post the full SQL statement?

You might quoting the identifiers as a possible workaround. For example:

SELECT COUNT("I"."PX_INVOICE_ID")
FROM "MYSCHEMA"."INVOICES" I;

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jimbo" <jamesferris@.hotmail.comwrote in message
news:1180626867.763717.260180@.j4g2000prf.googlegro ups.com...

Quote:

Originally Posted by

Im having an issue with DTS and I dont know if anyone else has
experienced this...In my transformation I have a sql statement that
executes against oracle..in it I'm selecting COUNT(DISTINCT
I.PX_INVOICE_ID) ...however when the DTS package executes I get an
error stating that "DISTINCTI"."PX_INVOICE_ID" is an invalid
identifier..why is it concatenting this?
>
Thanks
>
Jim
>

|||It appears to be with the OLE data provider...when upgrading it to 10
it worked..also the ODBC driver for Oracle worked...really weird

-Jim

DTS vs BCP vs TSQL

Hi,

As I know, although DTS gives graphical view of the data transformation/conversion work flow, it tend to be slower than BCP and TSQL scripts. So, keeping all that in mind, can I still use DTS (b'cos its a good tool to illustrate what’s happening inside, easy to understand and maintain) but with TSQL scripts (i.e: insert into...) doing the operations instead of DTS copy column or vb scripting, to gain maximum performance provided by the TSQL scripts? If you have other view points pls advice., What is the possibility of calling BCP exe inside DTS ?.

Let me provide you info. about the work I need to do, following are the things I need to do;

1. SQL source to SQL destination (different servers)

2. Transformations same SQL server and the same database

3. SQL source to text files.

Thanx

According to what I found, Yes you can call BCP inside DTS, which is very handy. Furthermore BCP and copy column transformations gave almost same performance.

|||Based on what you need, you definitely will need DTS for the ransformation. BCP is just to prepare your data for bulk copy into staging tables. Scrub and transform your data using DTS

DTS transformation question

Hello,
I am having problems with a quite simple DTS package. I guess the solution is quite simple, but I can't see it.

E.q: I am importing a Excel sheet with 1-3 columns into a predefined table in SQL 7.0 (default values). The number of columns might however change from time to time. I have designed the DTS package transformation using VB script.

Function Main()
DTSDestination("A") = DTSSource("A")
DTSDestination("B") = DTSSource("B")
DTSDestination("C") = DTSSource("C")
Main = DTSTransformStat_OK
End Function

but when I execute the package linked to a Excel file just containing column A and C I get an error claiming that the DTSDestination("B") is missing.

I am looking for a method that will either ignore the error or ignore the command under execution.

My real DTS package has several columns that might be missing.

ThanksPost some examples of your data. How are you able to map to columns a and c skipping b in your destination ?|||Originally posted by rnealejr
Post some examples of your data. How are you able to map to columns a and c skipping b in your destination ?

I was thinking of something like this

Function Main()
DTSDestination("A") = DTSSource("A")
IF ( DTSSource("B") EXIST) Then
DTSDestination("B") = DTSSource("B")
ELSE
DTSDestination("B") = "Mydefaultvalue"
END IF
DTSDestination("C") = DTSSource("C")
Main = DTSTransformStat_OK
End Function

, but the logical expression ( DTSSource("B") EXIST) is not valid. I am looking for some command or expression that will handle this.

I have seen postings where the expression is like:
IF isNull( DTSSource("B")) Then ....
,but isNull is just checking for empty cells. In my case the column "B" is not available.|||I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.|||Originally posted by rnealejr
I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.

Thanks rnealejr

I just got feedback from the SQL Development Team. They say:

The ActiveX Script task includes a list of functions that you can use in the
DTS scripts.
Instead of
IF ( DTSSource("B") EXIST) Then
DTSDestination("B") = DTSSource("B")
ELSE
...

Try
IF not DTSSource("B") is nothing Then
DTSDestination("B") = DTSSource("B")
ELSE
...
I have not been able to try this yet, but hopfully it will work. I will give you feedback if it does. Thanks again for your time.|||Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

Let me know if you are successful.|||Originally posted by rnealejr
Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

Let me know if you are successful.

Hi,

It did not work.

It seems that the Source columns are validated before the VBscript is executed.

Lasse