Re: Calling all Custom DTS Gurus!
- From: "bl..@xxxxxxxxx" <benlewing@xxxxxxxxx>
- Date: Thu, 6 Mar 2008 11:24:41 -0800 (PST)
I have an additional comlpexity.
I need to check within an existing DTS package on how to check if the
source field exists before I map to a destination field.
I get error messages and the vb script doesn't work. See below. Some
of the Destinations don't exist, how can I filter those out?
Function Main()
DTSDestination("Legacy App")=DTSSource("Legacy App")
If exists (DTSDestination("Legacy Number")) Then
DTSDestination("Legacy Number")=DTSSource("Legacy Number")
end if
Main = DTSTransformStat_OK
End Function
Any help will be great.
Thank you
James Croshaw wrote:
Darren Green <darren.green@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:<geG8ODEeQVc8Ew9n@xxxxxxxxxx>....
In article <d2602a.0202181023.9554e04@xxxxxxxxxxxxxxxxxx>, James Croshaw
<croshaj1@xxxxxxxxxxx> writes
<snip>
<snip>
This should be a simple error to fix but everything in the script
seems to check out just fine. Everything is in the right place and the
VB Script that is built is correct and syntactically checks out
perfectly. If I save the package before I execute it, it will open up
in Enterprise Manager just fine yet when I go to edit the data
tranformation it gives me the error: "Some of your transformations
refer to columns that do not exist in your source or destination."
Then it gives the usual three options: 1. Remove invalid
transformations, 2. Change Source/Destination. 3. Remove all
transformations and redo automapping. Plus if I build the DTS package
manually using the same script it works perfectly. I have even reverse
engineered a correct and working manually constructed DTS package
(with the same VB Script code for the transformation) but everything
seemed to match up just fine.
The error you mention above would indicate to me that everything is
definitely not "built is correct", although it may be syntactically
correct.
I can't spot anything definite because the script you posted will not
execute without some major modifications, and is clearly incomplete.
aFields is not defined so I get no SourceColumns collection which would
seem quite important as this may be the cause of the error.
ReturnFieldOrdinal is also missing, but relying on the extra code you've
added at the bottom, the script does not match you DestinationColumns
collection. The fields Telephone2 and HomeAddress3 are missing from the
script, which may cause errors during execution. Also is ImportGUID a
unqiueidentifier type, as the format is not valid.
You have also missed setting any column properties for both source and
destination columns. Properties such as Flags, Size, DataType,
Precision, NumericScale, Nullable are normally set.
A more complete script would be useful, preferably as an attachment so I
don't have to deal with the line wraps introduced by news clients.
Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool
for SQL 7.0 ? If not I strongly recommend you take a look as this will
hopefully show you everything you need to know.
(http://support.microsoft.com/default.aspx?scid=kb;en-us;Q239454)
Sounds a very interesting project, good luck!
Darren,
Thanks for your help. I have discovered that there is a bug in the
script which I introduced while debugging so my original post wasn't
quite true. It was just a minor problem which related to the Trim and
Left functions that I added into the VB Script. It seems to be working
fine right now, but I am having problems with Fixed Width templates
now as the definitions of the SourceColumns collection does not seem
to define the column widths/boundaries properly.
I have added the following code for fixed width and set the source
connection properties correctly as they should be e.g.:
.ConnectionProperties("Data Source").Value = sFilename
.ConnectionProperties("Mode").Value = 1 ' ANSI
.ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH
.ConnectionProperties("File Type").Value = 1
.ConnectionProperties("Number of Column").Value = 0
.ConnectionProperties("Row Delimiter").Value = vbCrLf
.ConnectionProperties("Column Delimiter").Value = "" ' Just Empty
String for Fixed Width
.ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier
for Fixed Width
...
iOrdinal = 1
' Loop through and add in all source columns
For iRowCount = 0 To UBound(aFields,2)
sFieldValue = aFields(0,iRowCount)
iFieldID = aFields(3,iRowCount)
' Only add if not a placeholder field
If CStr(iFieldID) <> "0" Then
sColumnName = "col" & Right("000" & CStr(iRowCount+1),3)
.SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal)
' Set the size of fixed width field
.SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount)
iOrdinal = iOrdinal + 1
End If
Next
...
As you suggested in your reply, fixed width file format would
definitely require the definition of the size property for each
SourceColumn defined. Yet even if this is correctly set it doesn't
seem to set the column boundaries when I save the Package to SQL
Server and consequently does not run.
Anyone out there know what has to be set to define the column
boundaries properly for a fixed width file?
Many thanks in advance,
James
XL Technologies.
- Prev by Date: Re: DTS to import data from web page
- Next by Date: RE: RUN A STORED PROCEDURE IN VBSCRIPT
- Previous by thread: Re: DTS to import data from web page
- Next by thread: RE: RUN A STORED PROCEDURE IN VBSCRIPT
- Index(es):
Relevant Pages
|
