I have had two strange problems with DTS in SQL Server 2000. If anyone can
shed any light, that would be great.

The first is in a Data Driven Query task, which reads from a flat file and
writes to SQL server. It's very simple, with a Text File (Source), and a
Microsoft OLE DB Provider for SQL Server, and a Data Driven Query task in
the middle.

When I go to the Transformations tab of the task, and look at the ActiveX
script transformation (with the Edit button), the Source Columns tab and the
Binding Columns tab have the columns mixed up! The Source Columns list
Col001 through Col010 (or whatever) under Available Columns, and Col010
through Col001 in Selected columns.

If I delete all the selected columns and re-add them so they are in the
right order, then save the package, then look again, they are flipped again.

And the text file is read from right to left when the package runs.

The "Binding Columns", on the other hand, are usually in a random order, not
just flipped upside down.

It appears that if I leave the Selected Columns empty for both Source and
Binding columns, the thing works correctly. All of the column definitions
are spelled out in the ActiveX script anyway.

Second: I have a lookup that says:
SELECT SSN_TIN
FROM Transactions
WHERE (SSN_TIN = ?) AND (ACCT_NUMBER = ?) AND (SEQ_NUM = ?) AND (Settle_Date
= ?)

I want Settle_Date third, not fourth; I place it third, and the DTS query
designer moves it back to fourth! What's up with that? Why is SQL deciding
that this should be the fourth parameter, not the third? I know what order
I pass the parms in the ActiveX script.

This happens on two computers, with Windows 2000 SP3, SQL 2000 developer
edition with SP3, and I can't believe that I'm the only one who has seen
this -- but I haven't been able to find a mention of this by anyone else.

Thanks.

David Walker

Does this happen with a datapump task as well ?

I have never seen it before but maybe you can post a very simple repro using Pubs

This way I can exactly repro what you do.


--

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

To reproduce this problem with a source text file and a DDQ task:
In Enterprise Manager, select the Server group, DTS, Local Packages, right
click - New Package (of course).

The first connection is Text File/Source. Select a text file; in my case it
was tab delimited and has at least 9 columns (mine had one more column than
the number of fields in the destination table. I don't know if that's
important). It doesn't matter much what's in this file, because some of the
problems can be seen before you actually run the package.

The next connection is Microsoft OLE DB Provider for SQL Server. Select the
Pubs database, and the authors table is OK.

Select Data Driven Query task.

In Properties:

Source tab: the text file.

Bindings tab: The Pubs database.

Transformations tab: Here it gets fun. Highlight the set of arrows (or pick
the named transformation) and click Edit. (The arrows should be "gathered"
into one arrow going across and then split again.)

Then click Properties; this generates the ActiveX script.

The first thing that's odd is that the generated ActiveX script is created
bottom to top! Mine said this:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************
' Copy each source column to the destination column
Function Main()
DTSDestination("contract") = DTSSource("Col009")
DTSDestination("zip") = DTSSource("Col008")
DTSDestination("state") = DTSSource("Col007")
DTSDestination("city") = DTSSource("Col006")
DTSDestination("address") = DTSSource("Col005")
DTSDestination("phone") = DTSSource("Col004")
DTSDestination("au_fname") = DTSSource("Col003")
DTSDestination("au_lname") = DTSSource("Col002")
DTSDestination("au_id") = DTSSource("Col001")
Main = DTSTransformstat_InsertQuery
End Function

Now this isn't necessarily wrong, just strange. And it makes me uneasy.... I
wouldn't have written the code to create the script this way.

Marvel at this, then click OK, then OK, then OK. You're looking at the
package editing screen again with the source connection, the destination
connection, and the task.

Double click the task again.

Click the Transformations tab.

Highlight the set of arrows and click Edit.

**Look at the Source Columns and Binding Columns tabs.

If your system behaves like mine, the columns on the right side will be
backwards; listed bottom to top.

On my system, the Source Columns tab has Col001 through Col010 in the
Available Columns list, and *Col009 through Col001* backwards in the
Selected Columns list.

Similarly, the Destination Columns tab has "au_id" through "contract" in the
Available Columns list, and "contract" through "au_id" backwards in the
Selected Columns list. And we all know that the author ID comes first!

Now, if you remove all these field names from the Selected Columns list,
then put them all back in the right order, then click OK a couple of times,
then go back in to look again, the fields will be backwards again. No matter
what you do.

And lo and behold, when the columns were listed (in Selected) as Col009
through Col001, and you run the DTS, that's how the data is read from the
source text file. Right to left. But you can't get the system to LEAVE THEM
the way I put them! (You can't actually run the DTS without defining the
queries, but that's left as an exercise for the reader, as they say.)

I didn't do any other steps to the DTS -- no workflow stuff or anything.

It doesn't matter if the system has MS03-031 applied or not; I am using
named pipes, and it doesn't matter if the SQL server and the client are the
same machine or not. And I am using SQL 2000 developer edition SP3 for
this, on Windows 2000 Pro with SP3.

Thanks. I am interested to see if you (or anyone else) can reproduce this.

David Walker


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I meant to change the heading on the previous post to make it obvious that
it has the repro steps. I don't know about the data pump task; I don't use
that one.

I can't reproduce the second problem I mentioned -- where my database was
changing my lookup; I had:

SELECT SSN_TIN
FROM Transactions
WHERE (SSN_TIN = ?) AND (ACCT_NUMBER = ?) AND (Settle_Date = ?) AND (SEQ_NUM
= ?)

and after I saved and reopened the lookup, it said:

SELECT SSN_TIN
FROM Transactions
WHERE (SSN_TIN = ?) AND (ACCT_NUMBER = ?) AND (SEQ_NUM = ?) AND (Settle_Date
= ?)

This kept happening until I deleted and recreated the whole DTS. If I can
reproduce this one cleanly, I'll let you know. Maybe the problems are
related!

Thanks.

David Walker

OK
Here is what I did (Test 1)

1. Exported Authors from pubs to text file to give me the DDQ text file
(First row = Col Names and comma seperated)
2. Added a DDQ
3. Source = Text File
4. Bindings = authors table
5. Transformations are a many to many.
6. Double click on transform
7. AX Script looks good
8. Come all the way out
9. Go all the way back in
10. Everything looks good.

Test 2 (No Col Names in text File)

1. Source cols now list col001 - 009
2. They match properly in the AX Script
3. All the way out
4. All the way In
5. Everything looks fine


SQL Server 2000 SP3 Dev on Win2k SP3



--

----------------------------
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



"David Walker" <DWalker> wrote

Yep.

I deleted the T File connection
I deleted the column names from the T File
I added a T File Connection
I created a DataPump task.


--

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

You deleted the connection!

If you start a whole new *DTS Task* using an input text file with no column
headings, there's a chance you can reproduce my problem.

Thanks.

David Walker


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Well, too bad you can't reproduce it! I suppose if it was widespread, more
people would see it. Strange... although I see it consistently on three
machines, including one that I have reinstalled Windows 2000 and SQL server
on.

Oh well. Thanks for trying!

David Walker



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote