Friday, February 24, 2012
DTS: Transform Data Task with ActiveX
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 automatically "map" the transformations in Transform Data Task?
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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Sunday, February 19, 2012
DTS with temporary tables
Hello,
I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.
I read that in this case I can′t use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.
My Store is likely to his. Please help me.
INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,
Valor,idConcepto)
EXEC CalculosDiarios @.Area,@.Reporte,@.FechaIni,@.FechaFin,0
SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2)
+ LEFT(D.Fecha,8) + D.Valor as Dato
FROM
ReportesPlantilla RP
LEFT JOIN
##Resultados D
ON
RP.Planta = D.Planta
AND RP.Etapa = D.Etapa
AND RP.GrupoEquipo = D.GrupoEquipo
AND RP.Equipo = D.Equipo
AND RP.Concepto = D.Concepto
AND D.Fecha BETWEEN @.FechaIni AND @.FechaFin
Hi,
The error is:
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : Invalid Object Name '##Resultados'
When I run the Store in Query Analizer everything run OK.
|||That is what I thought was going on. Sometimes when working with the ##tempTables in DTS you will need to work around it. What I often do is something like this:
Create the ##temp table in query analyzer Create the DTS steps Then drop the ##temp table in query analyzer|||Great, It worked, thanks a lot....Friday, February 17, 2012
DTS w/dynamic source & query
into a single destination table. This by itself is no problem.
However, the name of the source tables change, so I'll need to base
the transform task on a global variable that I can update via external
code. Not sure how to do that. I'm ok with executing the package 10
times if there's 10 source tables.
The last unknown piece is modifying the query used for the transform.
There are 10 columns in the source table, but there are 12 columns in
the destination table. I must provide the 2 missing columns. They will
simply contain a year and month, ie. 05 2003.
I'm taking a bunch of source tables (for a given month and year) and
rolling them together into one destination table, and carrying over
the month and year. I assume the month and year would also be globals.
But I'm not sure how to incorporate them into the transform task since
it wants strict SQL syntax.
Any help is appreciated, thanks in advance!"znelson" <znelson@.hpis.net> wrote in message
news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> I'm looking for a way to transform the contents of n source tables
> into a single destination table. This by itself is no problem.
> However, the name of the source tables change, so I'll need to base
> the transform task on a global variable that I can update via external
> code. Not sure how to do that. I'm ok with executing the package 10
> times if there's 10 source tables.
> The last unknown piece is modifying the query used for the transform.
> There are 10 columns in the source table, but there are 12 columns in
> the destination table. I must provide the 2 missing columns. They will
> simply contain a year and month, ie. 05 2003.
> I'm taking a bunch of source tables (for a given month and year) and
> rolling them together into one destination table, and carrying over
> the month and year. I assume the month and year would also be globals.
> But I'm not sure how to incorporate them into the transform task since
> it wants strict SQL syntax.
> Any help is appreciated, thanks in advance!
One way to pass in a global variable at runtime is as a parameter to
dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
you, then parameterize it using your preferred language:
http://www.sqldts.com/default.aspx?301
This is useful when the number of parameters is fairly limited. An
alternative if you have many global variables is to put the values in a
database table or .INI file, and use a Dynamic Properties task to retrieve
them and assign them to the variables. This allows for more complex logic,
conditional values based on server name, AD site name etc. Finally, you can
assign global variable values directly in an ActiveX task.
As for how to pass in the month/year to an INSERT, these pages may be
useful:
http://www.sqldts.com/?234
http://www.sqldts.com/?205
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<409a6999$1_2@.news.bluewin.ch>...
> "znelson" <znelson@.hpis.net> wrote in message
> news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> > I'm looking for a way to transform the contents of n source tables
> > into a single destination table. This by itself is no problem.
> > However, the name of the source tables change, so I'll need to base
> > the transform task on a global variable that I can update via external
> > code. Not sure how to do that. I'm ok with executing the package 10
> > times if there's 10 source tables.
> > The last unknown piece is modifying the query used for the transform.
> > There are 10 columns in the source table, but there are 12 columns in
> > the destination table. I must provide the 2 missing columns. They will
> > simply contain a year and month, ie. 05 2003.
> > I'm taking a bunch of source tables (for a given month and year) and
> > rolling them together into one destination table, and carrying over
> > the month and year. I assume the month and year would also be globals.
> > But I'm not sure how to incorporate them into the transform task since
> > it wants strict SQL syntax.
> > Any help is appreciated, thanks in advance!
> One way to pass in a global variable at runtime is as a parameter to
> dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
> you, then parameterize it using your preferred language:
> http://www.sqldts.com/default.aspx?301
> This is useful when the number of parameters is fairly limited. An
> alternative if you have many global variables is to put the values in a
> database table or .INI file, and use a Dynamic Properties task to retrieve
> them and assign them to the variables. This allows for more complex logic,
> conditional values based on server name, AD site name etc. Finally, you can
> assign global variable values directly in an ActiveX task.
> As for how to pass in the month/year to an INSERT, these pages may be
> useful:
> http://www.sqldts.com/?234
> http://www.sqldts.com/?205
> Simon
Simon,
Thanks a lot, very helpful!
DTS TRansform Task giving inconsistent results.
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.
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
> > > >
> > > >
> > >
> > >
> > >
>
>
dts transform task failing when using IBM db2 odbc driver..
I have the odbc driver that the server guys gave me..I installed it and then run a batch pgm, which i think does the database set up...in short i set up the DSN's in my local machine..I can use the dsn when i use the sql client...
I can't create a new package and then complete the task..Problem is when i try set up a transform task, i get a memory error...it says that the instruction referenced a memory location that can't be read...
So what i do is i use the import task in sql...which inturn uses the same odbc connection and runs the query on db2..inserts the results into a table in sql..then save the package...Only problem is that i can't edit the transorm task...same memory error...
Any idea why this happens ?? I would like to see how i can get past this using Odbc...What version of the driver are you using? IBM posts hotfixes and patches on their website for their drivers; you may be able to find a fix for the one you are using.
In particular I remember a hotfix for their AS/400 V5R1 driver that fixed an issue I was having.
Regards,
hmscott|||think it is ==>
Version: 7, Release: 2, Modification: 3, Service Level: WR21254
DTS Transform issue
Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?
If there is could you be specific in how to setup the DTS transformation script.
TIA
Jeffmoving thread to SQL Server forum (the SQL forum is for the SQL language itself)|||Import everything to a raw table first.
Then you can make all the validation you want before inserting the data into your system.|||I'd probably just suck the data from the external source into a working table that had pure Unicode (NVARCHAR) character columns. Once it was there, you can "sanitize" it any way you need to using Transact-SQL.
Another option that saves on disk and keeps the package conceptually "atomic" would be to handle the exceptions within the DTS package itself. Instead of using a default "flow" transformation within the DTS column mapping, you could use script to do whatever validation suited your needs.
-PatP|||HHmmm, well it seems like you both are saying the same thing.
"raw table" and "working table" are they the same thing?
All fileds are nvarchar correct?
and use a copy column to column transform correct?
JR
DTS Transform
column 6 ('CheckDate', dbType_dbtimestamp), status 6: dat over flow
invalid character value for cast specification
Here is the sql generated :
CREATE TABLE [DMS].[dbo].[Master] (
[Date_Entered] smalldatetime NULL,
[Initials] nvarchar (10) NULL,
[VendorName] nvarchar (50) NULL,
[CheckNum] nvarchar (20) NULL,
[ExpenseType] nvarchar (10) NULL,
[CheckDate] smalldatetime NULL,
[CheckAmount] money NULL
)
IM guessing i need to change date_enteed type or do a cast
any help pls
!~<Mis there anybody who has any idea about this?|||Is this the table DTS is inserting into, or is it actually trying to create the table? If it's trying to insert, change the CheckDate to datetime instead of smalldatetime and see if that fixes it.|||smalldatetime to datetime will not make a difference if value in the source is not a date. I'd suggest to change CheckDate and possibly Dtae_Entered to varchar(25), but add 2 calculated fields that would convert a valid date to date or NULL if it's not. Put them at the end of the table and do not insert anything into them (it'll fail if you try).
CREATE TABLE [DMS].[dbo].[Master] (
[Date_Entered] varchar(25) NULL,
[Initials] nvarchar (10) NULL,
[VendorName] nvarchar (50) NULL,
[CheckNum] nvarchar (20) NULL,
[ExpenseType] nvarchar (10) NULL,
[CheckDate] varchar(25) NULL,
[CheckAmount] money NULL,
[Date_Entered_Calc] as case when isdate([Date_Entered])=1 then cast([Date_Entered] as datetime) else cast(null as datetime) end,
[CheckDate_Calc] as case when isdate([CheckDate])=1 then cast([CheckDate] as datetime) else cast(null as datetime) end
)