by Davi » Fri, 05 Oct 2001 06:52:08
by Euan Garde » Sat, 06 Oct 2001 15:07:12
I don't have sample code handy but the pseudo code would look like this(from
memory so it might be a bit rough):
1. You need a global variable to control state.
Function Main
if GlobalVar <= 20 then
GlobalVar = GlobalVar + 1
'Do column mappings in here
Main = DTSTransformStat_SkipFetch
else
Global = 0
Main = DTSTransformStat_Ok
end if
End Function
--
-Euan
Please reply only to the newsgroup so that others can benefit.
When posting, please state the version of SQL Server being used and the
error number/exact error message text received, if any.
Hello, I'm the humble disclaimer. Nobody really likes me. I'm used to being
ignored and maligned - I still get the job done. If you cared enough to read
me, you'd learn that this posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use. ? 2001 Microsoft
Corporation. All rights reserved.
Quote:> I need to import a flat text file into a table in SQL
> server. Unfortunately the layout of the text file is
> repeated 20 times accross.
> In other words for each line I read from the text file, I
> need to break the record up into 20 lines in the SQL table.
> Can I do this in one single operation using some VB Script
> in an ActiveX transformation, or do I need to set up 20
> individual transformations with different a different
> transformation to pick up a different part of the text
> file. Then run them back to back?
1. DTS Transformation VB Script Problem - Your Brain on DTS
We have created a DTS package that consists of two connections, a VB Script
component, and an ActiveX Transformation. We declare a global variable in
the VB Script component called str_FISCAL_PERIOD, which is populated with
an Inputbox. Inspection of the value shows that the global variable is
indeed getting populated correctly. The second part of the package uses
ActiveX Scripting to move data from source to target with straight column
copy, except for the target.fiscal_period field which we are attempting to
populate with the value from the global variable. When we attempt to set
the value of that field to DTSGlobalVariable ("str_FISCAL_PERIOD").VALUE,
we get an error that states
"Error during Transformation 'DTSTransformation__6' for Row number 1.
Errors encountered so far in this task: 1. Error Source= Microsoft
VBScript runtime error. Error Description: Type mismatch:
'DTSGlobalVariable'"
It seems that in the Transformation ActiveX Script, DTS is thinking the
internal function name DTSGlobalVariable is a variable. We saw something
similar when we tried to use DTSLookups in a Transformation ActiveX Script
where the error message claimed that we had to declare the undefined
variable 'DTSLookups'.
This is the actual script from the ActiveX Transformation:
Function Main()
DTSDestination("FISCAL_PERIOD") = DTSGlobalVariable
("str_FISCAL_PERIOD").VALUE
Main = DTSTransformStat_SkipFetch
End Function
Does anyone have a clue what we are doing wrong?
Thanks in advance!
Kelly Rowe
Renaissance Worldwide, Inc.
2. Problem Creating InterMedia Index
3. DTS Transformation ActiveX Script Problem - Your Brain On DTS
5. Textfile DTS transformation that inserts nulls on SQL 2000
9. DTS Transformation is Too Slow to open between AS400 and SQL Server 2000
10. DTS Transformation steps and Perl
11. DTS Transformation from Excel
13. DTS Transformation Task failure