DTS: Transform Data Task with ActiveX

I trying to write out comment records in 40 byte chunks from a much longer
field(see below) but I can't find any way of writing out the records inside
the loop. The code below works (and the loop is being executed) but nothing
turns up in the output. Does anyone have any thoughts?
'***************************************
********************
' Visual Basic Transformation Script
'***************************************
********************
' Copy each source column to the destination column
dim Comment
dim CommentOut
dim CommentSub
Function Main()
if DTSSource("OrderID") <> DTSGlobalVariables("OrderID") then
' Process Comments
Comment = DTSGlobalVariables("Comment")
Do While len(Comment) > 0
if len(Comment) > 40 then
CommentOut = left(Comment,40)
CommentSub = mid(Comment,41,len(Comment)-40)
Comment = CommentSub
else
CommentOut = Comment
comment = ""
end if
DTSDestination("OrderID") = DTSGlobalVariables("OrderID")
DTSDestination("LineNumber") = DTSGlobalVariables("LineNumber")
DTSDestination("Comment") = CommentOut
Main = DTSTransformStat_OK
Loop
' Store Comment from the record causing the control break
DTSGlobalVariables("OrderID") = DTSSource("OrderID")
DTSGlobalVariables("Comment") = DTSSource("PackingNote")
end if
Main = DTSTransformStat_OK
End FunctionProblem sorted. Please ignore...
"Spike" wrote:

> I trying to write out comment records in 40 byte chunks from a much longer
> field(see below) but I can't find any way of writing out the records insid
e
> the loop. The code below works (and the loop is being executed) but nothi
ng
> turns up in the output. Does anyone have any thoughts?
> '***************************************
********************
> ' Visual Basic Transformation Script
> '***************************************
********************
> ' Copy each source column to the destination column
> dim Comment
> dim CommentOut
> dim CommentSub
>
> Function Main()
> if DTSSource("OrderID") <> DTSGlobalVariables("OrderID") then
> ' Process Comments
> Comment = DTSGlobalVariables("Comment")
> Do While len(Comment) > 0
> if len(Comment) > 40 then
> CommentOut = left(Comment,40)
> CommentSub = mid(Comment,41,len(Comment)-40)
> Comment = CommentSub
> else
> CommentOut = Comment
> comment = ""
> end if
> DTSDestination("OrderID") = DTSGlobalVariables("OrderID")
> DTSDestination("LineNumber") = DTSGlobalVariables("LineNumber")
> DTSDestination("Comment") = CommentOut
> Main = DTSTransformStat_OK
> Loop
> ' Store Comment from the record causing the control break
> DTSGlobalVariables("OrderID") = DTSSource("OrderID")
> DTSGlobalVariables("Comment") = DTSSource("PackingNote")
> end if
> Main = DTSTransformStat_OK
>
> End Function
>
>

DTS: How to call a sub-package by name only?

(SQL Server 2000, SP3a)
Hello all!
I've got a package that has several "Execute Package Task" elements. I'd like to make the
sub-packages version-independent. I ran across this blurb:
http://www.sqldts.com/default.aspx?216
Which talks about blanking the PackageID. I've done exactly as this has described (both
by blanking the PackageID with "Disconnected Edit" *and* by having a blank Global Variable
that I set the PackageIDs to). However, when I invoke my outer package, I keep getting an
"invalid GUID".
Any help would be greatly appreciated. Thanks!
John PetersonYou could of course simply use an Active Script task and call the package
that way OR an ExecuteProcess task with DTSRUN.
I have just followed the article and it works for me.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a package that has several "Execute Package Task" elements. I'd
like to make the
> sub-packages version-independent. I ran across this blurb:
> http://www.sqldts.com/default.aspx?216
> Which talks about blanking the PackageID. I've done exactly as this has
described (both
> by blanking the PackageID with "Disconnected Edit" *and* by having a blank
Global Variable
> that I set the PackageIDs to). However, when I invoke my outer package, I
keep getting an
> "invalid GUID".
> Any help would be greatly appreciated. Thanks!
> John Peterson
>|||Thanks Allan!
I *think* I might be having some issues with the blank global variable being "<not
displayable>". That is, when I use the Global Variable editor to "blank" out my PackageID
GV, everything works. However, if I save my package (as Structured Storage), exit the
package, and re-run it, I consistently get the "Invalid GUID specified" error. If I edit
the PackageID GV to blank it out again, then it works.
I even added a ActiveX task to set the PackageID GV:
' Ensure that the PackageID global variable is blank.
DTSGlobalVariables("PackageID").Value = ""
But upon initial load of the package, it still fails until I manually "dink" that GV.
If you have the time and inclination, could you try that and let me know of your results?
Many regards,
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> You could of course simply use an Active Script task and call the package
> that way OR an ExecuteProcess task with DTSRUN.
> I have just followed the article and it works for me.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I've got a package that has several "Execute Package Task" elements. I'd
> like to make the
> > sub-packages version-independent. I ran across this blurb:
> >
> > http://www.sqldts.com/default.aspx?216
> >
> > Which talks about blanking the PackageID. I've done exactly as this has
> described (both
> > by blanking the PackageID with "Disconnected Edit" *and* by having a blank
> Global Variable
> > that I set the PackageIDs to). However, when I invoke my outer package, I
> keep getting an
> > "invalid GUID".
> >
> > Any help would be greatly appreciated. Thanks!
> >
> > John Peterson
> >
> >
>|||What I think then is happening is that you are saying "" which is not empty.
Why do you have a GV setting the Package GUID in the first place if you do
not want to use it?
I would remove this altogether or if you want to occasionally specify the
GUID and sometimes not then I would do it in an Active Script task where i
could decide to use/or not based on a certain condition
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan!
> I *think* I might be having some issues with the blank global variable
being "<not
> displayable>". That is, when I use the Global Variable editor to "blank"
out my PackageID
> GV, everything works. However, if I save my package (as Structured
Storage), exit the
> package, and re-run it, I consistently get the "Invalid GUID specified"
error. If I edit
> the PackageID GV to blank it out again, then it works.
> I even added a ActiveX task to set the PackageID GV:
> ' Ensure that the PackageID global variable is blank.
> DTSGlobalVariables("PackageID").Value = ""
> But upon initial load of the package, it still fails until I manually
"dink" that GV.
> If you have the time and inclination, could you try that and let me know
of your results?
> Many regards,
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > You could of course simply use an Active Script task and call the
package
> > that way OR an ExecuteProcess task with DTSRUN.
> >
> > I have just followed the article and it works for me.
> >
> >
> > --
> >
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I've got a package that has several "Execute Package Task" elements.
I'd
> > like to make the
> > > sub-packages version-independent. I ran across this blurb:
> > >
> > > http://www.sqldts.com/default.aspx?216
> > >
> > > Which talks about blanking the PackageID. I've done exactly as this
has
> > described (both
> > > by blanking the PackageID with "Disconnected Edit" *and* by having a
blank
> > Global Variable
> > > that I set the PackageIDs to). However, when I invoke my outer
package, I
> > keep getting an
> > > "invalid GUID".
> > >
> > > Any help would be greatly appreciated. Thanks!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Sorry, I should have been more clear: I *always* want to call the sub-package by name
only, so I *do* want to use the GV to set the PackageID. The problem is, it seems like
DTS is sometimes replacing the actual string of "<not displayable>" for the PackageID when
I first load the package, which will then generate the "Invalid GUID specified" error. It
seems like I need to manually clear out the GV the first time I run it in the designer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> What I think then is happening is that you are saying "" which is not empty.
> Why do you have a GV setting the Package GUID in the first place if you do
> not want to use it?
> I would remove this altogether or if you want to occasionally specify the
> GUID and sometimes not then I would do it in an Active Script task where i
> could decide to use/or not based on a certain condition
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > Thanks Allan!
> >
> > I *think* I might be having some issues with the blank global variable
> being "<not
> > displayable>". That is, when I use the Global Variable editor to "blank"
> out my PackageID
> > GV, everything works. However, if I save my package (as Structured
> Storage), exit the
> > package, and re-run it, I consistently get the "Invalid GUID specified"
> error. If I edit
> > the PackageID GV to blank it out again, then it works.
> >
> > I even added a ActiveX task to set the PackageID GV:
> >
> > ' Ensure that the PackageID global variable is blank.
> > DTSGlobalVariables("PackageID").Value = ""
> >
> > But upon initial load of the package, it still fails until I manually
> "dink" that GV.
> >
> > If you have the time and inclination, could you try that and let me know
> of your results?
> >
> > Many regards,
> >
> > John Peterson
> >
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > You could of course simply use an Active Script task and call the
> package
> > > that way OR an ExecuteProcess task with DTSRUN.
> > >
> > > I have just followed the article and it works for me.
> > >
> > >
> > > --
> > >
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I've got a package that has several "Execute Package Task" elements.
> I'd
> > > like to make the
> > > > sub-packages version-independent. I ran across this blurb:
> > > >
> > > > http://www.sqldts.com/default.aspx?216
> > > >
> > > > Which talks about blanking the PackageID. I've done exactly as this
> has
> > > described (both
> > > > by blanking the PackageID with "Disconnected Edit" *and* by having a
> blank
> > > Global Variable
> > > > that I set the PackageIDs to). However, when I invoke my outer
> package, I
> > > keep getting an
> > > > "invalid GUID".
> > > >
> > > > Any help would be greatly appreciated. Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||John
If you never want to load the DTS package using either the Package and
Version GUID, then I'd do as Allan has suggested and put either,
a) Blank out the GUIDs using disconnected edit. They will only come back if
you edit that task in the designer.
b) Write a short ActiveScript task to blank out the GUIDs at the start of
the package.
Here is a script I use to accomplish that,
<-- Start -->
Dim oPkg
Dim colTasks
Dim sTsk
' open DTS package object so we can get the package details
Set oPkg = DTSGlobalVariables.Parent
' open object referring to Tasks collection
Set colTasks = oPkg.Tasks
' for each item in the collection
For Each sTsk in colTasks
' check if the task is an Execute Package task
If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
' create object referring to selected task
Set oTsk = oPkg.Tasks(sTsk.Name)
' blank out Package and VersionIDs so package loads by name only
' this will ensure that only the latest version is loaded.
oTsk.Properties("PackageID").Value = ""
oTsk.Properties("VersionID").Value = ""
' release object that was created
Set oTsk = Nothing
End If
Next
Set colTasks = Nothing
Set oPkg = Nothing
<-- End -->
--
Thanks
Phill
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Sorry, I should have been more clear: I *always* want to call the
sub-package by name
> only, so I *do* want to use the GV to set the PackageID. The problem is,
it seems like
> DTS is sometimes replacing the actual string of "<not displayable>" for
the PackageID when
> I first load the package, which will then generate the "Invalid GUID
specified" error. It
> seems like I need to manually clear out the GV the first time I run it in
the designer.
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> > What I think then is happening is that you are saying "" which is not
empty.
> >
> > Why do you have a GV setting the Package GUID in the first place if you
do
> > not want to use it?
> >
> > I would remove this altogether or if you want to occasionally specify
the
> > GUID and sometimes not then I would do it in an Active Script task where
i
> > could decide to use/or not based on a certain condition
> >
> > --
> >
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > > Thanks Allan!
> > >
> > > I *think* I might be having some issues with the blank global variable
> > being "<not
> > > displayable>". That is, when I use the Global Variable editor to
"blank"
> > out my PackageID
> > > GV, everything works. However, if I save my package (as Structured
> > Storage), exit the
> > > package, and re-run it, I consistently get the "Invalid GUID
specified"
> > error. If I edit
> > > the PackageID GV to blank it out again, then it works.
> > >
> > > I even added a ActiveX task to set the PackageID GV:
> > >
> > > ' Ensure that the PackageID global variable is blank.
> > > DTSGlobalVariables("PackageID").Value = ""
> > >
> > > But upon initial load of the package, it still fails until I manually
> > "dink" that GV.
> > >
> > > If you have the time and inclination, could you try that and let me
know
> > of your results?
> > >
> > > Many regards,
> > >
> > > John Peterson
> > >
> > >
> > >
> > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > > You could of course simply use an Active Script task and call the
> > package
> > > > that way OR an ExecuteProcess task with DTSRUN.
> > > >
> > > > I have just followed the article and it works for me.
> > > >
> > > >
> > > > --
> > > >
> > > > --
> > > >
> > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > I support PASS - the definitive, global community
> > > > for SQL Server professionals - http://www.sqlpass.org
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello all!
> > > > >
> > > > > I've got a package that has several "Execute Package Task"
elements.
> > I'd
> > > > like to make the
> > > > > sub-packages version-independent. I ran across this blurb:
> > > > >
> > > > > http://www.sqldts.com/default.aspx?216
> > > > >
> > > > > Which talks about blanking the PackageID. I've done exactly as
this
> > has
> > > > described (both
> > > > > by blanking the PackageID with "Disconnected Edit" *and* by having
a
> > blank
> > > > Global Variable
> > > > > that I set the PackageIDs to). However, when I invoke my outer
> > package, I
> > > > keep getting an
> > > > > "invalid GUID".
> > > > >
> > > > > Any help would be greatly appreciated. Thanks!
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Phil!
Ah -- I thought I might need to constantly set the PackageID to blank (hence the global
variable approach). But it sounds like I really only need to do this *once* with the
Disconnected Edit feature (unless I edit the task again, of course).
Thanks for the sample script!
John Peterson
"Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> John
> If you never want to load the DTS package using either the Package and
> Version GUID, then I'd do as Allan has suggested and put either,
> a) Blank out the GUIDs using disconnected edit. They will only come back if
> you edit that task in the designer.
> b) Write a short ActiveScript task to blank out the GUIDs at the start of
> the package.
> Here is a script I use to accomplish that,
> <-- Start -->
> Dim oPkg
> Dim colTasks
> Dim sTsk
> ' open DTS package object so we can get the package details
> Set oPkg = DTSGlobalVariables.Parent
> ' open object referring to Tasks collection
> Set colTasks = oPkg.Tasks
> ' for each item in the collection
> For Each sTsk in colTasks
> ' check if the task is an Execute Package task
> If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
> ' create object referring to selected task
> Set oTsk = oPkg.Tasks(sTsk.Name)
> ' blank out Package and VersionIDs so package loads by name only
> ' this will ensure that only the latest version is loaded.
> oTsk.Properties("PackageID").Value = ""
> oTsk.Properties("VersionID").Value = ""
> ' release object that was created
> Set oTsk = Nothing
> End If
> Next
> Set colTasks = Nothing
> Set oPkg = Nothing
> <-- End -->
> --
> Thanks
> Phill
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > Sorry, I should have been more clear: I *always* want to call the
> sub-package by name
> > only, so I *do* want to use the GV to set the PackageID. The problem is,
> it seems like
> > DTS is sometimes replacing the actual string of "<not displayable>" for
> the PackageID when
> > I first load the package, which will then generate the "Invalid GUID
> specified" error. It
> > seems like I need to manually clear out the GV the first time I run it in
> the designer.
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> > > What I think then is happening is that you are saying "" which is not
> empty.
> > >
> > > Why do you have a GV setting the Package GUID in the first place if you
> do
> > > not want to use it?
> > >
> > > I would remove this altogether or if you want to occasionally specify
> the
> > > GUID and sometimes not then I would do it in an Active Script task where
> i
> > > could decide to use/or not based on a certain condition
> > >
> > > --
> > >
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > > > Thanks Allan!
> > > >
> > > > I *think* I might be having some issues with the blank global variable
> > > being "<not
> > > > displayable>". That is, when I use the Global Variable editor to
> "blank"
> > > out my PackageID
> > > > GV, everything works. However, if I save my package (as Structured
> > > Storage), exit the
> > > > package, and re-run it, I consistently get the "Invalid GUID
> specified"
> > > error. If I edit
> > > > the PackageID GV to blank it out again, then it works.
> > > >
> > > > I even added a ActiveX task to set the PackageID GV:
> > > >
> > > > ' Ensure that the PackageID global variable is blank.
> > > > DTSGlobalVariables("PackageID").Value = ""
> > > >
> > > > But upon initial load of the package, it still fails until I manually
> > > "dink" that GV.
> > > >
> > > > If you have the time and inclination, could you try that and let me
> know
> > > of your results?
> > > >
> > > > Many regards,
> > > >
> > > > John Peterson
> > > >
> > > >
> > > >
> > > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > > news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > > > You could of course simply use an Active Script task and call the
> > > package
> > > > > that way OR an ExecuteProcess task with DTSRUN.
> > > > >
> > > > > I have just followed the article and it works for me.
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > --
> > > > >
> > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > I support PASS - the definitive, global community
> > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > I've got a package that has several "Execute Package Task"
> elements.
> > > I'd
> > > > > like to make the
> > > > > > sub-packages version-independent. I ran across this blurb:
> > > > > >
> > > > > > http://www.sqldts.com/default.aspx?216
> > > > > >
> > > > > > Which talks about blanking the PackageID. I've done exactly as
> this
> > > has
> > > > > described (both
> > > > > > by blanking the PackageID with "Disconnected Edit" *and* by having
> a
> > > blank
> > > > > Global Variable
> > > > > > that I set the PackageIDs to). However, when I invoke my outer
> > > package, I
> > > > > keep getting an
> > > > > > "invalid GUID".
> > > > > >
> > > > > > Any help would be greatly appreciated. Thanks!
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"...then you can remove the dependency on the PackageID, by blanking out the
PackageID property of the ExecutePackageTask... This can be done as a
one-off exercise with the Disconnected Edit feature..."
"If you regularly migrate packages and do not want to have to worry about
this, you can add a Dynamic Properties task to perform this during package
run-time..."
Is it not clear that you do this once, unless you change things?
Darren Green
http://www.sqldts.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> Thanks Phil!
> Ah -- I thought I might need to constantly set the PackageID to blank
(hence the global
> variable approach). But it sounds like I really only need to do this
*once* with the
> Disconnected Edit feature (unless I edit the task again, of course).
> Thanks for the sample script!
> John Peterson
>
> "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> > John
> >
> > If you never want to load the DTS package using either the Package and
> > Version GUID, then I'd do as Allan has suggested and put either,
> > a) Blank out the GUIDs using disconnected edit. They will only come back
if
> > you edit that task in the designer.
> > b) Write a short ActiveScript task to blank out the GUIDs at the start
of
> > the package.
> > Here is a script I use to accomplish that,
> >
> > <-- Start -->
> > Dim oPkg
> > Dim colTasks
> > Dim sTsk
> >
> > ' open DTS package object so we can get the package details
> > Set oPkg = DTSGlobalVariables.Parent
> >
> > ' open object referring to Tasks collection
> > Set colTasks = oPkg.Tasks
> >
> > ' for each item in the collection
> > For Each sTsk in colTasks
> >
> > ' check if the task is an Execute Package task
> > If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0
Then
> > ' create object referring to selected task
> > Set oTsk = oPkg.Tasks(sTsk.Name)
> >
> > ' blank out Package and VersionIDs so package loads by name only
> > ' this will ensure that only the latest version is loaded.
> > oTsk.Properties("PackageID").Value = ""
> > oTsk.Properties("VersionID").Value = ""
> >
> > ' release object that was created
> > Set oTsk = Nothing
> >
> > End If
> >
> > Next
> >
> > Set colTasks = Nothing
> > Set oPkg = Nothing
> >
> > <-- End -->
> >
> > --
> > Thanks
> > Phill
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > > Sorry, I should have been more clear: I *always* want to call the
> > sub-package by name
> > > only, so I *do* want to use the GV to set the PackageID. The problem
is,
> > it seems like
> > > DTS is sometimes replacing the actual string of "<not displayable>"
for
> > the PackageID when
> > > I first load the package, which will then generate the "Invalid GUID
> > specified" error. It
> > > seems like I need to manually clear out the GV the first time I run it
in
> > the designer.
> > >
> > >
> > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> > > > What I think then is happening is that you are saying "" which is
not
> > empty.
> > > >
> > > > Why do you have a GV setting the Package GUID in the first place if
you
> > do
> > > > not want to use it?
> > > >
> > > > I would remove this altogether or if you want to occasionally
specify
> > the
> > > > GUID and sometimes not then I would do it in an Active Script task
where
> > i
> > > > could decide to use/or not based on a certain condition
> > > >
> > > > --
> > > >
> > > > --
> > > >
> > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > I support PASS - the definitive, global community
> > > > for SQL Server professionals - http://www.sqlpass.org
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > > > > Thanks Allan!
> > > > >
> > > > > I *think* I might be having some issues with the blank global
variable
> > > > being "<not
> > > > > displayable>". That is, when I use the Global Variable editor to
> > "blank"
> > > > out my PackageID
> > > > > GV, everything works. However, if I save my package (as
Structured
> > > > Storage), exit the
> > > > > package, and re-run it, I consistently get the "Invalid GUID
> > specified"
> > > > error. If I edit
> > > > > the PackageID GV to blank it out again, then it works.
> > > > >
> > > > > I even added a ActiveX task to set the PackageID GV:
> > > > >
> > > > > ' Ensure that the PackageID global variable is blank.
> > > > > DTSGlobalVariables("PackageID").Value = ""
> > > > >
> > > > > But upon initial load of the package, it still fails until I
manually
> > > > "dink" that GV.
> > > > >
> > > > > If you have the time and inclination, could you try that and let
me
> > know
> > > > of your results?
> > > > >
> > > > > Many regards,
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > >
> > > > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > > > news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > > > > You could of course simply use an Active Script task and call
the
> > > > package
> > > > > > that way OR an ExecuteProcess task with DTSRUN.
> > > > > >
> > > > > > I have just followed the article and it works for me.
> > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > > I support PASS - the definitive, global community
> > > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > > >
> > > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > > > > > > (SQL Server 2000, SP3a)
> > > > > > >
> > > > > > > Hello all!
> > > > > > >
> > > > > > > I've got a package that has several "Execute Package Task"
> > elements.
> > > > I'd
> > > > > > like to make the
> > > > > > > sub-packages version-independent. I ran across this blurb:
> > > > > > >
> > > > > > > http://www.sqldts.com/default.aspx?216
> > > > > > >
> > > > > > > Which talks about blanking the PackageID. I've done exactly
as
> > this
> > > > has
> > > > > > described (both
> > > > > > > by blanking the PackageID with "Disconnected Edit" *and* by
having
> > a
> > > > blank
> > > > > > Global Variable
> > > > > > > that I set the PackageIDs to). However, when I invoke my
outer
> > > > package, I
> > > > > > keep getting an
> > > > > > > "invalid GUID".
> > > > > > >
> > > > > > > Any help would be greatly appreciated. Thanks!
> > > > > > >
> > > > > > > John Peterson
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in message
news:eivN0%23fTEHA.3476@.tk2msftngp13.phx.gbl...
> "...then you can remove the dependency on the PackageID, by blanking out the
> PackageID property of the ExecutePackageTask... This can be done as a
> one-off exercise with the Disconnected Edit feature..."
> "If you regularly migrate packages and do not want to have to worry about
> this, you can add a Dynamic Properties task to perform this during package
> run-time..."
> Is it not clear that you do this once, unless you change things?
It wasn't clear to me. Does "migrate packages" mean change them? Or move them from place
to place? Or how do I interpret that?
> --
> Darren Green
> http://www.sqldts.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> > Thanks Phil!
> >
> > Ah -- I thought I might need to constantly set the PackageID to blank
> (hence the global
> > variable approach). But it sounds like I really only need to do this
> *once* with the
> > Disconnected Edit feature (unless I edit the task again, of course).
> >
> > Thanks for the sample script!
> >
> > John Peterson
> >
> >
> >
> > "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> > news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> > > John
> > >
> > > If you never want to load the DTS package using either the Package and
> > > Version GUID, then I'd do as Allan has suggested and put either,
> > > a) Blank out the GUIDs using disconnected edit. They will only come back
> if
> > > you edit that task in the designer.
> > > b) Write a short ActiveScript task to blank out the GUIDs at the start
> of
> > > the package.
> > > Here is a script I use to accomplish that,
> > >
> > > <-- Start -->
> > > Dim oPkg
> > > Dim colTasks
> > > Dim sTsk
> > >
> > > ' open DTS package object so we can get the package details
> > > Set oPkg = DTSGlobalVariables.Parent
> > >
> > > ' open object referring to Tasks collection
> > > Set colTasks = oPkg.Tasks
> > >
> > > ' for each item in the collection
> > > For Each sTsk in colTasks
> > >
> > > ' check if the task is an Execute Package task
> > > If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0
> Then
> > > ' create object referring to selected task
> > > Set oTsk = oPkg.Tasks(sTsk.Name)
> > >
> > > ' blank out Package and VersionIDs so package loads by name only
> > > ' this will ensure that only the latest version is loaded.
> > > oTsk.Properties("PackageID").Value = ""
> > > oTsk.Properties("VersionID").Value = ""
> > >
> > > ' release object that was created
> > > Set oTsk = Nothing
> > >
> > > End If
> > >
> > > Next
> > >
> > > Set colTasks = Nothing
> > > Set oPkg = Nothing
> > >
> > > <-- End -->
> > >
> > > --
> > > Thanks
> > > Phill
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > > > Sorry, I should have been more clear: I *always* want to call the
> > > sub-package by name
> > > > only, so I *do* want to use the GV to set the PackageID. The problem
> is,
> > > it seems like
> > > > DTS is sometimes replacing the actual string of "<not displayable>"
> for
> > > the PackageID when
> > > > I first load the package, which will then generate the "Invalid GUID
> > > specified" error. It
> > > > seems like I need to manually clear out the GV the first time I run it
> in
> > > the designer.
> > > >
> > > >
> > > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > > news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> > > > > What I think then is happening is that you are saying "" which is
> not
> > > empty.
> > > > >
> > > > > Why do you have a GV setting the Package GUID in the first place if
> you
> > > do
> > > > > not want to use it?
> > > > >
> > > > > I would remove this altogether or if you want to occasionally
> specify
> > > the
> > > > > GUID and sometimes not then I would do it in an Active Script task
> where
> > > i
> > > > > could decide to use/or not based on a certain condition
> > > > >
> > > > > --
> > > > >
> > > > > --
> > > > >
> > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > I support PASS - the definitive, global community
> > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> > > > > > Thanks Allan!
> > > > > >
> > > > > > I *think* I might be having some issues with the blank global
> variable
> > > > > being "<not
> > > > > > displayable>". That is, when I use the Global Variable editor to
> > > "blank"
> > > > > out my PackageID
> > > > > > GV, everything works. However, if I save my package (as
> Structured
> > > > > Storage), exit the
> > > > > > package, and re-run it, I consistently get the "Invalid GUID
> > > specified"
> > > > > error. If I edit
> > > > > > the PackageID GV to blank it out again, then it works.
> > > > > >
> > > > > > I even added a ActiveX task to set the PackageID GV:
> > > > > >
> > > > > > ' Ensure that the PackageID global variable is blank.
> > > > > > DTSGlobalVariables("PackageID").Value = ""
> > > > > >
> > > > > > But upon initial load of the package, it still fails until I
> manually
> > > > > "dink" that GV.
> > > > > >
> > > > > > If you have the time and inclination, could you try that and let
> me
> > > know
> > > > > of your results?
> > > > > >
> > > > > > Many regards,
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > > > > news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> > > > > > > You could of course simply use an Active Script task and call
> the
> > > > > package
> > > > > > > that way OR an ExecuteProcess task with DTSRUN.
> > > > > > >
> > > > > > > I have just followed the article and it works for me.
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > > > I support PASS - the definitive, global community
> > > > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > > > >
> > > > > > >
> > > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > > news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> > > > > > > > (SQL Server 2000, SP3a)
> > > > > > > >
> > > > > > > > Hello all!
> > > > > > > >
> > > > > > > > I've got a package that has several "Execute Package Task"
> > > elements.
> > > > > I'd
> > > > > > > like to make the
> > > > > > > > sub-packages version-independent. I ran across this blurb:
> > > > > > > >
> > > > > > > > http://www.sqldts.com/default.aspx?216
> > > > > > > >
> > > > > > > > Which talks about blanking the PackageID. I've done exactly
> as
> > > this
> > > > > has
> > > > > > > described (both
> > > > > > > > by blanking the PackageID with "Disconnected Edit" *and* by
> having
> > > a
> > > > > blank
> > > > > > > Global Variable
> > > > > > > > that I set the PackageIDs to). However, when I invoke my
> outer
> > > > > package, I
> > > > > > > keep getting an
> > > > > > > > "invalid GUID".
> > > > > > > >
> > > > > > > > Any help would be greatly appreciated. Thanks!
> > > > > > > >
> > > > > > > > John Peterson
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

DTS: How to automatically "map" the transformations in Transform Data Task?

(SQL Server 2000, SP3a)
Hello all!
I'm considering using a Transform Data Task to move data from one server to another. I'd
like to programmatically set the query to the Transform Data Task to potentially different
(at run-time) tables. Is there a convenient way to programmatically wipe out the
transformations and reset them based on the current query?
Thanks!
John PetersonConvenient?
There is the object model. So long as you know your source definition then
sure you can do this.
You need to loop through the Transformation Objects - Remove them (Removes
any previous mappings)
You then need to add 1 or more Transformation Object(s)
Then then need to add 1 or more SourceColumn property values to the
transformation(s)
Then you need to add 1 or more DestinationColumn property values to the
transformation(s)
You then add the 1 or more Transformation objects to the Transformations
collection.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I'm considering using a Transform Data Task to move data from one server
to another. I'd
> like to programmatically set the query to the Transform Data Task to
potentially different
> (at run-time) tables. Is there a convenient way to programmatically wipe
out the
> transformations and reset them based on the current query?
> Thanks!
> John Peterson
>|||Thanks Allan!
I guess, then, that there's no method that is sort of like "auto map" based on the current
settings (where it would assume that the Source/Destination columns are exactly the same)?
Sort of like how the DTS Designer does it?
Barring that, do you have any examples of what you've just described below?
Thanks!
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> Convenient?
> There is the object model. So long as you know your source definition then
> sure you can do this.
> You need to loop through the Transformation Objects - Remove them (Removes
> any previous mappings)
> You then need to add 1 or more Transformation Object(s)
> Then then need to add 1 or more SourceColumn property values to the
> transformation(s)
> Then you need to add 1 or more DestinationColumn property values to the
> transformation(s)
> You then add the 1 or more Transformation objects to the Transformations
> collection.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I'm considering using a Transform Data Task to move data from one server
> to another. I'd
> > like to programmatically set the query to the Transform Data Task to
> potentially different
> > (at run-time) tables. Is there a convenient way to programmatically wipe
> out the
> > transformations and reset them based on the current query?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||If you have not changed the meta data i.e.
source column names and destination column names are the same then no
remapping is required. DTS will do that for you.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan!
> I guess, then, that there's no method that is sort of like "auto map"
based on the current
> settings (where it would assume that the Source/Destination columns are
exactly the same)?
> Sort of like how the DTS Designer does it?
> Barring that, do you have any examples of what you've just described
below?
> Thanks!
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > Convenient?
> >
> > There is the object model. So long as you know your source definition
then
> > sure you can do this.
> >
> > You need to loop through the Transformation Objects - Remove them
(Removes
> > any previous mappings)
> > You then need to add 1 or more Transformation Object(s)
> > Then then need to add 1 or more SourceColumn property values to the
> > transformation(s)
> > Then you need to add 1 or more DestinationColumn property values to the
> > transformation(s)
> >
> > You then add the 1 or more Transformation objects to the Transformations
> > collection.
> >
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I'm considering using a Transform Data Task to move data from one
server
> > to another. I'd
> > > like to programmatically set the query to the Transform Data Task to
> > potentially different
> > > (at run-time) tables. Is there a convenient way to programmatically
wipe
> > out the
> > > transformations and reset them based on the current query?
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Hey Allan!
I may have been unclear. I essentially want to execute a package where I would be
programmatically specifying a table name (or query) to the Transform Data Task. That is,
at run-time, my table will be different than what may have been used at design-time with
the Transform Data Task. As such, I would assume that I'd need to tear down the existing
transformations and add the new ones, no?
Thanks again for your help!
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> If you have not changed the meta data i.e.
> source column names and destination column names are the same then no
> remapping is required. DTS will do that for you.
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > Thanks Allan!
> >
> > I guess, then, that there's no method that is sort of like "auto map"
> based on the current
> > settings (where it would assume that the Source/Destination columns are
> exactly the same)?
> > Sort of like how the DTS Designer does it?
> >
> > Barring that, do you have any examples of what you've just described
> below?
> >
> > Thanks!
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > Convenient?
> > >
> > > There is the object model. So long as you know your source definition
> then
> > > sure you can do this.
> > >
> > > You need to loop through the Transformation Objects - Remove them
> (Removes
> > > any previous mappings)
> > > You then need to add 1 or more Transformation Object(s)
> > > Then then need to add 1 or more SourceColumn property values to the
> > > transformation(s)
> > > Then you need to add 1 or more DestinationColumn property values to the
> > > transformation(s)
> > >
> > > You then add the 1 or more Transformation objects to the Transformations
> > > collection.
> > >
> > >
> > > --
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I'm considering using a Transform Data Task to move data from one
> server
> > > to another. I'd
> > > > like to programmatically set the query to the Transform Data Task to
> > > potentially different
> > > > (at run-time) tables. Is there a convenient way to programmatically
> wipe
> > > out the
> > > > transformations and reset them based on the current query?
> > > >
> > > > Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||Not if the query definition remains the same and does not invalidate the
metadata
i.e.
Server 1 Source
SELECT a,b,c FROM D
So long as the Source from your dynamic changes has the same metadata as
this above query you are OK. Same goes for the destination.
If you changed your sourcestatement to
SELECT name, age, address1 FROM MyNewTable
Then everything would break.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e2ms47iYEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Hey Allan!
> I may have been unclear. I essentially want to execute a package where I
would be
> programmatically specifying a table name (or query) to the Transform Data
Task. That is,
> at run-time, my table will be different than what may have been used at
design-time with
> the Transform Data Task. As such, I would assume that I'd need to tear
down the existing
> transformations and add the new ones, no?
> Thanks again for your help!
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > If you have not changed the meta data i.e.
> >
> > source column names and destination column names are the same then no
> > remapping is required. DTS will do that for you.
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > > Thanks Allan!
> > >
> > > I guess, then, that there's no method that is sort of like "auto map"
> > based on the current
> > > settings (where it would assume that the Source/Destination columns
are
> > exactly the same)?
> > > Sort of like how the DTS Designer does it?
> > >
> > > Barring that, do you have any examples of what you've just described
> > below?
> > >
> > > Thanks!
> > >
> > >
> > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > Convenient?
> > > >
> > > > There is the object model. So long as you know your source
definition
> > then
> > > > sure you can do this.
> > > >
> > > > You need to loop through the Transformation Objects - Remove them
> > (Removes
> > > > any previous mappings)
> > > > You then need to add 1 or more Transformation Object(s)
> > > > Then then need to add 1 or more SourceColumn property values to the
> > > > transformation(s)
> > > > Then you need to add 1 or more DestinationColumn property values to
the
> > > > transformation(s)
> > > >
> > > > You then add the 1 or more Transformation objects to the
Transformations
> > > > collection.
> > > >
> > > >
> > > > --
> > > > --
> > > >
> > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > I support PASS - the definitive, global community
> > > > for SQL Server professionals - http://www.sqlpass.org
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello all!
> > > > >
> > > > > I'm considering using a Transform Data Task to move data from one
> > server
> > > > to another. I'd
> > > > > like to programmatically set the query to the Transform Data Task
to
> > > > potentially different
> > > > > (at run-time) tables. Is there a convenient way to
programmatically
> > wipe
> > > > out the
> > > > > transformations and reset them based on the current query?
> > > > >
> > > > > Thanks!
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Allan! That's exactly what I'm trying to say: I might define my query in the DTS
Designer as:
select * from A
But I want to programmatically change the query at run-time to be:
select * from B
It sounds like I need to tear down the transformations and rebuild them based on B. Do
you have some examples of such a thing?
Thanks again!
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OqDHpFlYEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Not if the query definition remains the same and does not invalidate the
> metadata
> i.e.
> Server 1 Source
> SELECT a,b,c FROM D
>
> So long as the Source from your dynamic changes has the same metadata as
> this above query you are OK. Same goes for the destination.
> If you changed your sourcestatement to
> SELECT name, age, address1 FROM MyNewTable
> Then everything would break.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e2ms47iYEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > Hey Allan!
> >
> > I may have been unclear. I essentially want to execute a package where I
> would be
> > programmatically specifying a table name (or query) to the Transform Data
> Task. That is,
> > at run-time, my table will be different than what may have been used at
> design-time with
> > the Transform Data Task. As such, I would assume that I'd need to tear
> down the existing
> > transformations and add the new ones, no?
> >
> > Thanks again for your help!
> >
> > John Peterson
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > If you have not changed the meta data i.e.
> > >
> > > source column names and destination column names are the same then no
> > > remapping is required. DTS will do that for you.
> > >
> > > --
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > > > Thanks Allan!
> > > >
> > > > I guess, then, that there's no method that is sort of like "auto map"
> > > based on the current
> > > > settings (where it would assume that the Source/Destination columns
> are
> > > exactly the same)?
> > > > Sort of like how the DTS Designer does it?
> > > >
> > > > Barring that, do you have any examples of what you've just described
> > > below?
> > > >
> > > > Thanks!
> > > >
> > > >
> > > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > > Convenient?
> > > > >
> > > > > There is the object model. So long as you know your source
> definition
> > > then
> > > > > sure you can do this.
> > > > >
> > > > > You need to loop through the Transformation Objects - Remove them
> > > (Removes
> > > > > any previous mappings)
> > > > > You then need to add 1 or more Transformation Object(s)
> > > > > Then then need to add 1 or more SourceColumn property values to the
> > > > > transformation(s)
> > > > > Then you need to add 1 or more DestinationColumn property values to
> the
> > > > > transformation(s)
> > > > >
> > > > > You then add the 1 or more Transformation objects to the
> Transformations
> > > > > collection.
> > > > >
> > > > >
> > > > > --
> > > > > --
> > > > >
> > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > I support PASS - the definitive, global community
> > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > I'm considering using a Transform Data Task to move data from one
> > > server
> > > > > to another. I'd
> > > > > > like to programmatically set the query to the Transform Data Task
> to
> > > > > potentially different
> > > > > > (at run-time) tables. Is there a convenient way to
> programmatically
> > > wipe
> > > > > out the
> > > > > > transformations and reset them based on the current query?
> > > > > >
> > > > > > Thanks!
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||SELECT * is really bad as it may break if you start to add/remove attributes
+ it presupposes you want all attributes as well.
If you have varying attribute names then yes I have an example of this
Reads a Query in a file
Removes transformations
Creates the XL worksheet
remaps attributes
If you want it then mail me privately and you can have it.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OQ7woYpYEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks Allan! That's exactly what I'm trying to say: I might define my
query in the DTS
> Designer as:
> select * from A
> But I want to programmatically change the query at run-time to be:
> select * from B
> It sounds like I need to tear down the transformations and rebuild them
based on B. Do
> you have some examples of such a thing?
> Thanks again!
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:OqDHpFlYEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Not if the query definition remains the same and does not invalidate the
> > metadata
> >
> > i.e.
> >
> > Server 1 Source
> >
> > SELECT a,b,c FROM D
> >
> >
> > So long as the Source from your dynamic changes has the same metadata as
> > this above query you are OK. Same goes for the destination.
> >
> > If you changed your sourcestatement to
> >
> > SELECT name, age, address1 FROM MyNewTable
> >
> > Then everything would break.
> >
> >
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:e2ms47iYEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > > Hey Allan!
> > >
> > > I may have been unclear. I essentially want to execute a package
where I
> > would be
> > > programmatically specifying a table name (or query) to the Transform
Data
> > Task. That is,
> > > at run-time, my table will be different than what may have been used
at
> > design-time with
> > > the Transform Data Task. As such, I would assume that I'd need to
tear
> > down the existing
> > > transformations and add the new ones, no?
> > >
> > > Thanks again for your help!
> > >
> > > John Peterson
> > >
> > >
> > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > If you have not changed the meta data i.e.
> > > >
> > > > source column names and destination column names are the same then
no
> > > > remapping is required. DTS will do that for you.
> > > >
> > > > --
> > > > --
> > > >
> > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > I support PASS - the definitive, global community
> > > > for SQL Server professionals - http://www.sqlpass.org
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > > > > Thanks Allan!
> > > > >
> > > > > I guess, then, that there's no method that is sort of like "auto
map"
> > > > based on the current
> > > > > settings (where it would assume that the Source/Destination
columns
> > are
> > > > exactly the same)?
> > > > > Sort of like how the DTS Designer does it?
> > > > >
> > > > > Barring that, do you have any examples of what you've just
described
> > > > below?
> > > > >
> > > > > Thanks!
> > > > >
> > > > >
> > > > > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > > > > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > > > Convenient?
> > > > > >
> > > > > > There is the object model. So long as you know your source
> > definition
> > > > then
> > > > > > sure you can do this.
> > > > > >
> > > > > > You need to loop through the Transformation Objects - Remove
them
> > > > (Removes
> > > > > > any previous mappings)
> > > > > > You then need to add 1 or more Transformation Object(s)
> > > > > > Then then need to add 1 or more SourceColumn property values to
the
> > > > > > transformation(s)
> > > > > > Then you need to add 1 or more DestinationColumn property values
to
> > the
> > > > > > transformation(s)
> > > > > >
> > > > > > You then add the 1 or more Transformation objects to the
> > Transformations
> > > > > > collection.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > --
> > > > > >
> > > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > > I support PASS - the definitive, global community
> > > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > > >
> > > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > > > > (SQL Server 2000, SP3a)
> > > > > > >
> > > > > > > Hello all!
> > > > > > >
> > > > > > > I'm considering using a Transform Data Task to move data from
one
> > > > server
> > > > > > to another. I'd
> > > > > > > like to programmatically set the query to the Transform Data
Task
> > to
> > > > > > potentially different
> > > > > > > (at run-time) tables. Is there a convenient way to
> > programmatically
> > > > wipe
> > > > > > out the
> > > > > > > transformations and reset them based on the current query?
> > > > > > >
> > > > > > > Thanks!
> > > > > > >
> > > > > > > John Peterson
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Dts.log in script task

Hi

Can someone tell me where I can find the log file created by the Dts.log method in script task.

I have created a log provider file as Mylog.xml, but the messages recorded are from the Dts.Event.FireInformation method and not the Dts.log method.

I don't know where the messages are filed.

Regards

Baldev

To log the output of Dts.Log() to a Log Provider, go to the "Configure SSIS Logs" dialog (e.g. SSIS/Logging...). Change the Logging Mode on the Script Task from the default of "UseParentSetting" to "Enabled". That is , click on the check box next to the Script task until it is checked ( LoggingMode = "Enabled") and not checked and greyed out (LoggingMode = "UseParentSetting") or unchecked (LoggingMode = "Disabled").

With the Script Task node selected, navigate to the Details tab, and select the "ScriptTaskLogEntry" event. Dts.Log() calls will now be sent to whatever log providers are enabled for the Script Task itself. Also, make sure to select the log provider for the script task "again", since this is effectively overriding the parent containers logging settings.

|||

Great, that's exactly what I wanted.

Thanks a lot

Baldev

DTS, finish execution of the package

I want to finsih the execution of a DTS package from an ActiveX task. If a condition is ok, the package would continue as normal. If not, I want to finish the package without any error. Just without executing the next tasks. Do you have any idea?
ThanksIs the same case I posted before.. but, is there any way to define diferent flows in a DTS packet? I mean by using boolean global variables or someting like that, and without using the success/failure workflows.

Sorry about my english, thanks in advance.

DTS zipping a file

Hello there.
I have this DTS that creates 5 files. The last step on the DTS is anActiveX task that moves the files to a certain folder. Bun?t now what I really need to do is zip the file and leave it on the server.

This DTS is executed by a job on the SQL Server, and the files are created on a network share with access to the SQLServer user that executes the job.

I've been trying to find out information on how to zip the folder and I've come up with two options:

1. install winzip on the server and run winzip32.exe passing arguments to it.
2. use the standard zipfldr.dll that comes with Windows to do the job.

Number one is out of my reach since IT department don't want the software installed, I have to stick with number 2.

Now I've read that using rundll32.exe I can run the dll as an exe, but I need the entry point to the dll wich I cannot find besides the RouteTheCall %L, which actually opens an explorer window with the folder or zip file I pass to it as an argument.

Does anyone know if this is possible either using rundll32.exe or automating the task through an AttiveX task?

TIA.

I would really like to find the answer to this question as well but, I don't see any replies and I noticed that this posting is 8 months old. I also could not find any information on the web as of yet. If I find anything I will respond to this post.

Steve

|||I recently think of the 2nd method. But it's too difficult. If you find how to use the zipfldr.dll .Please tell me. Thanks a lot. And , I will tell me if I get a clear using.|||

I programming to compress the data. I want to call am usable zipfldr.dll with C#.NET programming, eager to know that must use which order in the calling uses. please tell me at jenkarn_7@.hotmail.com, thanks.

|||

I just searched for "zip file" on msdn... http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/567402.aspx

|||Easiest method of compressing files is to use rar.exe (WinRar CLI).

rar -a C:\FilesToCompress\*.xxx C:\MyCompressedfile.rar

Can run that through a CLI in SSIS and DTS.

DTS zipping a file

Hello there.
I have this DTS that creates 5 files. The last step on the DTS is anActiveX task that moves the files to a certain folder. Bun?t now what I really need to do is zip the file and leave it on the server.

This DTS is executed by a job on the SQL Server, and the files are created on a network share with access to the SQLServer user that executes the job.

I've been trying to find out information on how to zip the folder and I've come up with two options:

1. install winzip on the server and run winzip32.exe passing arguments to it.
2. use the standard zipfldr.dll that comes with Windows to do the job.

Number one is out of my reach since IT department don't want the software installed, I have to stick with number 2.

Now I've read that using rundll32.exe I can run the dll as an exe, but I need the entry point to the dll wich I cannot find besides the RouteTheCall %L, which actually opens an explorer window with the folder or zip file I pass to it as an argument.

Does anyone know if this is possible either using rundll32.exe or automating the task through an AttiveX task?

TIA.

I would really like to find the answer to this question as well but, I don't see any replies and I noticed that this posting is 8 months old. I also could not find any information on the web as of yet. If I find anything I will respond to this post.

Steve

|||I recently think of the 2nd method. But it's too difficult. If you find how to use the zipfldr.dll .Please tell me. Thanks a lot. And , I will tell me if I get a clear using.|||

I programming to compress the data. I want to call am usable zipfldr.dll with C#.NET programming, eager to know that must use which order in the calling uses. please tell me at jenkarn_7@.hotmail.com, thanks.

|||

I just searched for "zip file" on msdn... http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/567402.aspx

|||Easiest method of compressing files is to use rar.exe (WinRar CLI).

rar -a C:\FilesToCompress\*.xxx C:\MyCompressedfile.rar

Can run that through a CLI in SSIS and DTS.

DTS zipping a file

Hello there.
I have this DTS that creates 5 files. The last step on the DTS is anActiveX task that moves the files to a certain folder. Bun?t now what I really need to do is zip the file and leave it on the server.

This DTS is executed by a job on the SQL Server, and the files are created on a network share with access to the SQLServer user that executes the job.

I've been trying to find out information on how to zip the folder and I've come up with two options:

1. install winzip on the server and run winzip32.exe passing arguments to it.
2. use the standard zipfldr.dll that comes with Windows to do the job.

Number one is out of my reach since IT department don't want the software installed, I have to stick with number 2.

Now I've read that using rundll32.exe I can run the dll as an exe, but I need the entry point to the dll wich I cannot find besides the RouteTheCall %L, which actually opens an explorer window with the folder or zip file I pass to it as an argument.

Does anyone know if this is possible either using rundll32.exe or automating the task through an AttiveX task?

TIA.

I would really like to find the answer to this question as well but, I don't see any replies and I noticed that this posting is 8 months old. I also could not find any information on the web as of yet. If I find anything I will respond to this post.

Steve

|||I recently think of the 2nd method. But it's too difficult. If you find how to use the zipfldr.dll .Please tell me. Thanks a lot. And , I will tell me if I get a clear using.|||

I programming to compress the data. I want to call am usable zipfldr.dll with C#.NET programming, eager to know that must use which order in the calling uses. please tell me at jenkarn_7@.hotmail.com, thanks.

|||

I just searched for "zip file" on msdn... http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/567402.aspx

|||Easiest method of compressing files is to use rar.exe (WinRar CLI).

rar -a C:\FilesToCompress\*.xxx C:\MyCompressedfile.rar

Can run that through a CLI in SSIS and DTS.

DTS xp..sendmail error

Similar problem was discussed here very recently but that didn't solved my problem.

Inside a DTS package, I have a ActiveX task that checks for a file at some location, on success it should send me a mail using xp..sendmail and on failure it should send me a mail.
The mail task on success works but the mailing task on failure does not work.
Please help.

-RohitPost the activex code.|||here is the ActiveX code.
======================================
Function Main()
Dim fs, MyFile
Dim FileLocation
Dim FileName
Dim FileInfo
Dim mm
Dim dd

if Len(month(date)) < 2 then
mm = trim("0" & month(date))
else
mm = trim(month(date))
end if

if Len(day(date)) < 2 then
dd = trim("0" & day(date))
else
dd = trim(day(date))
end if

FileName = "Invoice" & mm & dd & year(date) &".txt"
FileLocation = DTSGlobalVariables("Invoice_Input_Location").Value
FileInfo = FileLocation & FileName

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(FileInfo)) Then
fs.MoveFile (FileInfo),(FileLocation & "ATLAS_Invoice.txt")
set fs=nothing
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If

End Function|||What version of sql server are you using ?|||Microsoft SQL Server 2000 - 8.00.876 (Intel X86)|||Insert "exit function" after each "Main=DTSTaskExecResult_XXX".|||using "exit function" also was not working, I tried unchecking "Fail package on first error" on the DTS package properties and its now working.

Thanks for your help.|||Yes - that will do it. What made you set that ? Basically, nothing will run after that step has failed.|||I checked it because I wanted the package to stop executing in case of a failure, never knew it wont even run the task that should run with a failure.

DTS TRansform Task giving inconsistent results.

Hi,
This is an interesting problem...I have a simple Select statement that will
work perfectly, producing all the right values for the fields as required,
but I get two different results, depending on if I execute the task on its
own,or if it is part of the whole package being executed. Run standalone, it
works fine. As part of thePackage, one of the fields does not populate.
Weird, huh? Anyone got a clue?
Cheers
Wal
Can you show us your data and SELECT statement?
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> Hi,
> This is an interesting problem...I have a simple Select statement that
will
> work perfectly, producing all the right values for the fields as required,
> but I get two different results, depending on if I execute the task on its
> own,or if it is part of the whole package being executed. Run standalone,
it
> works fine. As part of thePackage, one of the fields does not populate.
> Weird, huh? Anyone got a clue?
> Cheers
>
|||Hi,
here's the SQL, but I cant show you the data - client confidentiality etc...
SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
FROM dbo.tblFact_Contact LEFT JOIN
dbo.tblFact_CaseParts ON
dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
WHERE dbo.tblFact_Contact.Contact_Status_id = 2
Thanks
"Uri Dimant" wrote:

> Wal
> Can you show us your data and SELECT statement?
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> will
> it
>
>
|||Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:
[vbcol=seagreen]
> Hi,
> here's the SQL, but I cant show you the data - client confidentiality etc...
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
|||Don't use the SELECT * syntax, even if you do also specify named columns.
DTS parses the field list at design time. If for whatever reason, if the
ordinal positions change, you will get odd results. The reason the manual
process works is because the * is reparsed befor execution.
Sincerely,
Anthony Thomas

"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:

> Hi,
> here's the SQL, but I cant show you the data - client confidentiality
etc...[vbcol=seagreen]
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
required,[vbcol=seagreen]
its[vbcol=seagreen]
standalone,[vbcol=seagreen]
populate.[vbcol=seagreen]
|||Thanks Anthony. It's really nice to know why it gave me problems.
Rgds
Warren
"AnthonyThomas" wrote:

> Don't use the SELECT * syntax, even if you do also specify named columns.
> DTS parses the field list at design time. If for whatever reason, if the
> ordinal positions change, you will get odd results. The reason the manual
> process works is because the * is reparsed befor execution.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
> Hi Uri,
> I have fixed the problem by deleting the task and recreating
> it...infuriating, cos I dont know what caused the problem, so it mght
> rec-cur.. Thanks for your interest, Uri
>
> Cheers
> "Wal" wrote:
> etc...
> required,
> its
> standalone,
> populate.
>
>

DTS TRansform Task giving inconsistent results.

Hi,
This is an interesting problem...I have a simple Select statement that will
work perfectly, producing all the right values for the fields as required,
but I get two different results, depending on if I execute the task on its
own,or if it is part of the whole package being executed. Run standalone, it
works fine. As part of thePackage, one of the fields does not populate.
Weird, huh? Anyone got a clue?
CheersWal
Can you show us your data and SELECT statement?
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> Hi,
> This is an interesting problem...I have a simple Select statement that
will
> work perfectly, producing all the right values for the fields as required,
> but I get two different results, depending on if I execute the task on its
> own,or if it is part of the whole package being executed. Run standalone,
it
> works fine. As part of thePackage, one of the fields does not populate.
> Weird, huh? Anyone got a clue?
> Cheers
>|||Hi,
here's the SQL, but I cant show you the data - client confidentiality etc...
SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
FROM dbo.tblFact_Contact LEFT JOIN
dbo.tblFact_CaseParts ON
dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
WHERE dbo.tblFact_Contact.Contact_Status_id = 2
Thanks
"Uri Dimant" wrote:
> Wal
> Can you show us your data and SELECT statement?
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > Hi,
> >
> > This is an interesting problem...I have a simple Select statement that
> will
> > work perfectly, producing all the right values for the fields as required,
> > but I get two different results, depending on if I execute the task on its
> > own,or if it is part of the whole package being executed. Run standalone,
> it
> > works fine. As part of thePackage, one of the fields does not populate.
> > Weird, huh? Anyone got a clue?
> > Cheers
> >
> >
>
>|||Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:
> Hi,
> here's the SQL, but I cant show you the data - client confidentiality etc...
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
> > Wal
> > Can you show us your data and SELECT statement?
> >
> > "Wal" <Wal@.discussions.microsoft.com> wrote in message
> > news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > > Hi,
> > >
> > > This is an interesting problem...I have a simple Select statement that
> > will
> > > work perfectly, producing all the right values for the fields as required,
> > > but I get two different results, depending on if I execute the task on its
> > > own,or if it is part of the whole package being executed. Run standalone,
> > it
> > > works fine. As part of thePackage, one of the fields does not populate.
> > > Weird, huh? Anyone got a clue?
> > > Cheers
> > >
> > >
> >
> >
> >|||Don't use the SELECT * syntax, even if you do also specify named columns.
DTS parses the field list at design time. If for whatever reason, if the
ordinal positions change, you will get odd results. The reason the manual
process works is because the * is reparsed befor execution.
Sincerely,
Anthony Thomas
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:
> Hi,
> here's the SQL, but I cant show you the data - client confidentiality
etc...
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
> > Wal
> > Can you show us your data and SELECT statement?
> >
> > "Wal" <Wal@.discussions.microsoft.com> wrote in message
> > news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > > Hi,
> > >
> > > This is an interesting problem...I have a simple Select statement that
> > will
> > > work perfectly, producing all the right values for the fields as
required,
> > > but I get two different results, depending on if I execute the task on
its
> > > own,or if it is part of the whole package being executed. Run
standalone,
> > it
> > > works fine. As part of thePackage, one of the fields does not
populate.
> > > Weird, huh? Anyone got a clue?
> > > Cheers
> > >
> > >
> >
> >
> >|||Thanks Anthony. It's really nice to know why it gave me problems.
Rgds
Warren
"AnthonyThomas" wrote:
> Don't use the SELECT * syntax, even if you do also specify named columns.
> DTS parses the field list at design time. If for whatever reason, if the
> ordinal positions change, you will get odd results. The reason the manual
> process works is because the * is reparsed befor execution.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
> Hi Uri,
> I have fixed the problem by deleting the task and recreating
> it...infuriating, cos I dont know what caused the problem, so it mght
> rec-cur.. Thanks for your interest, Uri
>
> Cheers
> "Wal" wrote:
> > Hi,
> >
> > here's the SQL, but I cant show you the data - client confidentiality
> etc...
> >
> > SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> > FROM dbo.tblFact_Contact LEFT JOIN
> > dbo.tblFact_CaseParts ON
> > dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> > WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> >
> > Thanks
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> > > Wal
> > > Can you show us your data and SELECT statement?
> > >
> > > "Wal" <Wal@.discussions.microsoft.com> wrote in message
> > > news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > > > Hi,
> > > >
> > > > This is an interesting problem...I have a simple Select statement that
> > > will
> > > > work perfectly, producing all the right values for the fields as
> required,
> > > > but I get two different results, depending on if I execute the task on
> its
> > > > own,or if it is part of the whole package being executed. Run
> standalone,
> > > it
> > > > works fine. As part of thePackage, one of the fields does not
> populate.
> > > > Weird, huh? Anyone got a clue?
> > > > Cheers
> > > >
> > > >
> > >
> > >
> > >
>
>