Basically i have a bunch of excel files that must be uploaded to the
database about one a week so i'm trying to create a Dynamic DTS
package. The first thing i need to do is convert the files over to
text(CSV) then upload them to the database. Right now i'm stuck on the
conversion. I have 3 global variables setup as XlsFile <Source>,
CSVFile<Destination> and Columns<list of all the columns> which i can
set in a sp when i run the DTS Pkg. The problem is that the
Destination Columns are not getting updated so the Transformation
Mapping is bombing. Thanks in advance.


Here is the code im using:


i = 1

' Set Package information
SET PKG = DTSGlobalVariables.Parent
Set dPump = PKG.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
Set tform = dpump.Transformations.New("DTSPump.DataPumpTransfo rmCopy")

' Clear out old mappings
for each tform in dpump.Transformations
dpump.Transformations.Remove tform.Name
next

' Source Global Variable
set conTextFile = pkg.Connections("ExcelDocument")
conTextFile.DataSource = "F:\Shared\vSourceUploads\ztest.xls"

' Destination Global Variable
set conTextFile2 = pkg.Connections("CvsFile")
conTextFile2.DataSource = "F:\Shared\vSourceUploads\ztestcvs.cvs"

' Set Columns from Global Variable
Columns = "ONE,TWO,THREE,FOUR,FIVE,SIX,"

' loop thru and set up mapping
Do While Len(Columns) > 0

' loop thru list and set up mapping
xCol = Left(Columns, InStr(1, Columns, ",") - 1)
Columns = Right(Columns, Len(Columns) - InStr(1, Columns,
","))


If xCol <> "" Then



' start mappin
Set tform = dpump.Transformations.New("DTSPump.DataPumpTransfo rmCopy")

tform.Name = "DTSTransformation__"&i
tform.TransformFlags = 63
tform.ForceSourceBlobsBuffered = 0
tform.ForceBlobsInMemory = False
tform.InMemoryBlobSize = 1048576
tform.TransformPhases = 4


' Start Mapping
Set oColumn = tform.SourceColumns.New(xCol , 1)
oColumn.Name = xCol
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

tform.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = tform.DestinationColumns.New(xCol , 1)
oColumn.Name = xCol
oColumn.Ordinal = 1
oColumn.Flags = 0
oColumn.Size = 255
oColumn.DataType = 8
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

tform.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = tform.TransformServerProperties
Set oTransProps = Nothing

dPump.Transformations.Add tform

END IF

i=i+1
Loop


Set dPump = Nothing
set tform = Nothing
set pkg = Nothing

Hi CRAIG:
I think the problem is not "Destination Columns are not getting updated
so the Transformation Mapping is bombing" but caused by the code redo and It
may take bad effect of the databind. you may add
If Not IsPostBack Then
End If
to your code and may try again
Best Wishes
Wei Ci Zhou

Hi CRAIG:
I think the problem is not "Destination Columns are not getting updated
so the Transformation Mapping is bombing" but caused by the code redo and It
may take bad effect of the databind. you may add
If Not IsPostBack Then
End If
to your code and may try again
Best Wishes
Wei Ci Zhou

This is a Script inside DTS using the ActiveX Component i don't
understand what u mean. Where would i put the IsPostBack? Everything
seems to work except the destination columns it just uses the sameones
over and over never puts in the columns from the xls sheet i pass in.
Thanks.



"Wei Ci Zhou" <weicizhou (AT) hotmail (DOT) com.discuss> wrote

Hello Craig,

Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with
direct assistance from a Microsoft Support Professional through
Microsoft Product Support Services. You can contact Microsoft Product
Support directly to discuss additional support options you may have
available, by contacting us at 1-(800)936-5800 or by choosing one of
the options listed at
http://support.microsoft.com/default...d=sz;en-us;top. If this
is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact
you directly, please send email to (remove "online." from this no
Spam email address): mailto:dscommhf (AT) online (DOT) microsoft.com with the
following information,

*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address


Thanks for using MSDN Newsgroup.

Vikrant Dalwale

Microsoft SQL Server Support Professional


Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer’s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.



--------------------
TNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news-spur1.maxwe
ll.syr.edu!news.maxwell.syr.edu!postnews1.google.c om!not-for-mail