|
|
You're in the
sql server dts
group:dynamic source columns
sql server dts:
Every time I importing a text file the columns name change. I can collect all the source columns in a string, when I call the pkg I pass this string as GV. In my Transformation I want to change change the name of the source columns. How can I do this ? Tks JFB
Tks for you reply and help Allan,
Can i do this steps dynamicly? I have this for script on the transformation, ir works if I run the package manually, but If I change the source file the source columns remain there. Also the table has a primary key. Rgds JFB '********************************************************************** ' Visual Basic Transformation Script '************************************************************************ ' Copy each source column to the destination column Function Main() 'Declare Variables Dim SourceArray, DestiantionArray, count, temp, Field 'Get Field values from GVs arrays SourceArray = Split(DTSGlobalVariables("SourceFields").Value, ",") DestinationArray = Split(DTSGlobalVariables("DestinationFields").Value, ",") count = 0 'Initialize counter 'Loop thru array and import columns to table For Each Field In SourceArray If Not IsNull(Field) then 'MsgBox Field DTSDestination(DestinationArray(count)) = DTSSource(Field) End if count = count + 1 next Main = DTSTransformStat_OK End Function [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > I would do it like this > > 1. Remove existing transforms > 2. Create a new transformation object for each source column > 3. Add the SourceColumn to the Transformation > 4. Add a DestinationColumn to the transformation > 5. Add the transformation to the transformations collection > > > Allan > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > Hi All, > > Every time I importing a text file the columns name change. > > I can collect all the source columns in a string, when I call the pkg I pass > > this string as GV. > > In my Transformation I want to change change the name of the source columns. > > How can I do this ? > > Tks > > JFB >
Do you remember the posted subject name?
Otherwise I will keep searching for it Tks JFB [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > Yes you can do it dynamagically. > > I have posted before about a script I wrote in ActiveX task that took a > Query, Parsed it, created a table in Excel and the created the mappings. > > Allan > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > Tks for you reply and help Allan, > > Can i do this steps dynamicly? > > I have this for script on the transformation, ir works if I run the package > > manually, but If I change the source file the source columns remain there. > > Also the table has a primary key. > > Rgds > > JFB > > > > '********************************************************************** > > ' Visual Basic Transformation Script > > '************************************************************************ > > > > ' Copy each source column to the destination column > > Function Main() > > 'Declare Variables > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > 'Get Field values from GVs arrays > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, ",") > > DestinationArray = Split(DTSGlobalVariables("DestinationFields").Value, > > ",") > > > > count = 0 'Initialize counter > > > > 'Loop thru array and import columns to table > > For Each Field In SourceArray > > If Not IsNull(Field) then > > 'MsgBox Field > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > End if > > count = count + 1 > > next > > > > Main = DTSTransformStat_OK > > End Function > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > I would do it like this > > > > > > 1. Remove existing transforms > > > 2. Create a new transformation object for each source column > > > 3. Add the SourceColumn to the Transformation > > > 4. Add a DestinationColumn to the transformation > > > 5. Add the transformation to the transformations collection > > > > > > > > > Allan > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > Hi All, > > > > Every time I importing a text file the columns name change. > > > > I can collect all the source columns in a string, when I call the pkg I > > pass > > > > this string as GV. > > > > In my Transformation I want to change change the name of the source > > columns. > > > > How can I do this ? > > > > Tks > > > > JFB > > > >
Tks Allan,
I really appreciate you help. j.balladares@verizon.net Rgds JFB [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > Throw me a mail and I'll post the package > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > Do you remember the posted subject name? > > Otherwise I will keep searching for it > > Tks > > JFB > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > Yes you can do it dynamagically. > > > > > > I have posted before about a script I wrote in ActiveX task that took a > > > Query, Parsed it, created a table in Excel and the created the mappings. > > > > > > Allan > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > Tks for you reply and help Allan, > > > > Can i do this steps dynamicly? > > > > I have this for script on the transformation, ir works if I run the > > package > > > > manually, but If I change the source file the source columns remain > > there. > > > > Also the table has a primary key. > > > > Rgds > > > > JFB > > > > > > > > '********************************************************************** > > > > ' Visual Basic Transformation Script > > > > > > '************************************************************************ > > > > > > > > ' Copy each source column to the destination column > > > > Function Main() > > > > 'Declare Variables > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > 'Get Field values from GVs arrays > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, ",") > > > > DestinationArray = Split(DTSGlobalVariables("DestinationFields").Value, > > > > ",") > > > > > > > > count = 0 'Initialize counter > > > > > > > > 'Loop thru array and import columns to table > > > > For Each Field In SourceArray > > > > If Not IsNull(Field) then > > > > 'MsgBox Field > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > End if > > > > count = count + 1 > > > > next > > > > > > > > Main = DTSTransformStat_OK > > > > End Function > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > I would do it like this > > > > > > > > > > 1. Remove existing transforms > > > > > 2. Create a new transformation object for each source column > > > > > 3. Add the SourceColumn to the Transformation > > > > > 4. Add a DestinationColumn to the transformation > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > Hi All, > > > > > > Every time I importing a text file the columns name change. > > > > > > I can collect all the source columns in a string, when I call the > > pkg I > > > > pass > > > > > > this string as GV. > > > > > > In my Transformation I want to change change the name of the source > > > > columns. > > > > > > How can I do this ? > > > > > > Tks > > > > > > JFB > > > > > > > > >
Allan,
Tks for the example... now I have another problem. Sometimes we want to use the same source column in two diferent destination columns. I'm getting an error saying the Tranformatio_columName already exist. How can I fix that? Rgds JFB [quoted text, click to view] "JFB" <help@jfb.com> wrote in message news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > Tks Allan, > I really appreciate you help. j.balladares@verizon.net > Rgds > JFB > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > Throw me a mail and I'll post the package > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > Do you remember the posted subject name? > > > Otherwise I will keep searching for it > > > Tks > > > JFB > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > Yes you can do it dynamagically. > > > > > > > > I have posted before about a script I wrote in ActiveX task that took > a > > > > Query, Parsed it, created a table in Excel and the created the > mappings. > > > > > > > > Allan > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > Tks for you reply and help Allan, > > > > > Can i do this steps dynamicly? > > > > > I have this for script on the transformation, ir works if I run the > > > package > > > > > manually, but If I change the source file the source columns remain > > > there. > > > > > Also the table has a primary key. > > > > > Rgds > > > > > JFB > > > > > > > > > > > '********************************************************************** > > > > > ' Visual Basic Transformation Script > > > > > > > > > '************************************************************************ > > > > > > > > > > ' Copy each source column to the destination column > > > > > Function Main() > > > > > 'Declare Variables > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > 'Get Field values from GVs arrays > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, ",") > > > > > DestinationArray = > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > ",") > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > 'Loop thru array and import columns to table > > > > > For Each Field In SourceArray > > > > > If Not IsNull(Field) then > > > > > 'MsgBox Field > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > End if > > > > > count = count + 1 > > > > > next > > > > > > > > > > Main = DTSTransformStat_OK > > > > > End Function > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > I would do it like this > > > > > > > > > > > > 1. Remove existing transforms > > > > > > 2. Create a new transformation object for each source column > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > Hi All, > > > > > > > Every time I importing a text file the columns name change. > > > > > > > I can collect all the source columns in a string, when I call > the > > > pkg I > > > > > pass > > > > > > > this string as GV. > > > > > > > In my Transformation I want to change change the name of the > source > > > > > columns. > > > > > > > How can I do this ? > > > > > > > Tks > > > > > > > JFB > > > > > > > > > > > > > >
I would do it like this
1. Remove existing transforms 2. Create a new transformation object for each source column 3. Add the SourceColumn to the Transformation 4. Add a DestinationColumn to the transformation 5. Add the transformation to the transformations collection Allan [quoted text, click to view] "JFB" <help@jfb.com> wrote in message news:help@jfb.com:
> Hi All, > Every time I importing a text file the columns name change. > I can collect all the source columns in a string, when I call the pkg I pass > this string as GV. > In my Transformation I want to change change the name of the source columns. > How can I do this ? > Tks > JFB
Yes you can do it dynamagically.
I have posted before about a script I wrote in ActiveX task that took a Query, Parsed it, created a table in Excel and the created the mappings. Allan [quoted text, click to view] "JFB" <help@jfb.com> wrote in message news:help@jfb.com:
> Tks for you reply and help Allan, > Can i do this steps dynamicly? > I have this for script on the transformation, ir works if I run the package > manually, but If I change the source file the source columns remain there. > Also the table has a primary key. > Rgds > JFB > > '********************************************************************** > ' Visual Basic Transformation Script > '************************************************************************ > > ' Copy each source column to the destination column > Function Main() > 'Declare Variables > Dim SourceArray, DestiantionArray, count, temp, Field > > 'Get Field values from GVs arrays > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, ",") > DestinationArray = Split(DTSGlobalVariables("DestinationFields").Value, > ",") > > count = 0 'Initialize counter > > 'Loop thru array and import columns to table > For Each Field In SourceArray > If Not IsNull(Field) then > 'MsgBox Field > DTSDestination(DestinationArray(count)) = DTSSource(Field) > End if > count = count + 1 > next > > Main = DTSTransformStat_OK > End Function > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > I would do it like this > > > > 1. Remove existing transforms > > 2. Create a new transformation object for each source column > > 3. Add the SourceColumn to the Transformation > > 4. Add a DestinationColumn to the transformation > > 5. Add the transformation to the transformations collection > > > > > > Allan > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > Hi All, > > > Every time I importing a text file the columns name change. > > > I can collect all the source columns in a string, when I call the pkg I > pass > > > this string as GV. > > > In my Transformation I want to change change the name of the source > columns. > > > How can I do this ? > > > Tks > > > JFB > >
Throw me a mail and I'll post the package
[quoted text, click to view] "JFB" <help@jfb.com> wrote in message news:help@jfb.com:
> Do you remember the posted subject name? > Otherwise I will keep searching for it > Tks > JFB > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > Yes you can do it dynamagically. > > > > I have posted before about a script I wrote in ActiveX task that took a > > Query, Parsed it, created a table in Excel and the created the mappings. > > > > Allan > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > Tks for you reply and help Allan, > > > Can i do this steps dynamicly? > > > I have this for script on the transformation, ir works if I run the > package > > > manually, but If I change the source file the source columns remain > there. > > > Also the table has a primary key. > > > Rgds > > > JFB > > > > > > '********************************************************************** > > > ' Visual Basic Transformation Script > > > > '************************************************************************ > > > > > > ' Copy each source column to the destination column > > > Function Main() > > > 'Declare Variables > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > 'Get Field values from GVs arrays > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, ",") > > > DestinationArray = Split(DTSGlobalVariables("DestinationFields").Value, > > > ",") > > > > > > count = 0 'Initialize counter > > > > > > 'Loop thru array and import columns to table > > > For Each Field In SourceArray > > > If Not IsNull(Field) then > > > 'MsgBox Field > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > End if > > > count = count + 1 > > > next > > > > > > Main = DTSTransformStat_OK > > > End Function > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > I would do it like this > > > > > > > > 1. Remove existing transforms > > > > 2. Create a new transformation object for each source column > > > > 3. Add the SourceColumn to the Transformation > > > > 4. Add a DestinationColumn to the transformation > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > Allan > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > Hi All, > > > > > Every time I importing a text file the columns name change. > > > > > I can collect all the source columns in a string, when I call the > pkg I > > > pass > > > > > this string as GV. > > > > > In my Transformation I want to change change the name of the source > > > columns. > > > > > How can I do this ? > > > > > Tks > > > > > JFB > > > > > >
It is relatively easy to do.
The best way would be to design a package in designer that does what you want and then save as a VB module. Have a look in there at how DTS itself does it. Allan [quoted text, click to view] "JFB" <help@jfb.com> wrote in message news:help@jfb.com:
> Allan, > Tks for the example... now I have another problem. > Sometimes we want to use the same source column in two diferent destination > columns. > I'm getting an error saying the Tranformatio_columName already exist. > How can I fix that? > Rgds > JFB > > "JFB" <help@jfb.com> wrote in message > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > Tks Allan, > > I really appreciate you help. j.balladares@verizon.net > > Rgds > > JFB > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > Throw me a mail and I'll post the package > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > Do you remember the posted subject name? > > > > Otherwise I will keep searching for it > > > > Tks > > > > JFB > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > Yes you can do it dynamagically. > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > took > > a > > > > > Query, Parsed it, created a table in Excel and the created the > > mappings. > > > > > > > > > > Allan > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > Can i do this steps dynamicly? > > > > > > I have this for script on the transformation, ir works if I run > the > > > > package > > > > > > manually, but If I change the source file the source columns > remain > > > > there. > > > > > > Also the table has a primary key. > > > > > > Rgds > > > > > > JFB > > > > > > > > > > > > > > '********************************************************************** > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > Function Main() > > > > > > 'Declare Variables > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > ",") > > > > > > DestinationArray = > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > ",") > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > For Each Field In SourceArray > > > > > > If Not IsNull(Field) then > > > > > > 'MsgBox Field > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > End if > > > > > > count = count + 1 > > > > > > next > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > I would do it like this > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > 2. Create a new transformation object for each source column > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > Hi All, > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > I can collect all the source columns in a string, when I call > > the > > > > pkg I > > > > > > pass > > > > > > > > this string as GV. > > > > > > > > In my Transformation I want to change change the name of the > > source > > > > > > columns. > > > > > > > > How can I do this ? > > > > > > > > Tks > > > > > > > > JFB > > > > > > > > > > > > > > > > > > >
Hi Allan,
i have a similar problem: Importing text files and do not know in advance how many columns the files have. Can you send me your example too ? THANS so much ! JulianeB@Hotmail.com Juliane [quoted text, click to view] "Allan Mitchell" wrote:
> It is relatively easy to do. > > The best way would be to design a package in designer that does what you > want and then save as a VB module. Have a look in there at how DTS > itself does it. > > Allan > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > Allan, > > Tks for the example... now I have another problem. > > Sometimes we want to use the same source column in two diferent destination > > columns. > > I'm getting an error saying the Tranformatio_columName already exist. > > How can I fix that? > > Rgds > > JFB > > > > "JFB" <help@jfb.com> wrote in message > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > > Tks Allan, > > > I really appreciate you help. j.balladares@verizon.net > > > Rgds > > > JFB > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > > Throw me a mail and I'll post the package > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > Do you remember the posted subject name? > > > > > Otherwise I will keep searching for it > > > > > Tks > > > > > JFB > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > > Yes you can do it dynamagically. > > > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > > took > > > a > > > > > > Query, Parsed it, created a table in Excel and the created the > > > mappings. > > > > > > > > > > > > Allan > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > > Can i do this steps dynamicly? > > > > > > > I have this for script on the transformation, ir works if I run > > the > > > > > package > > > > > > > manually, but If I change the source file the source columns > > remain > > > > > there. > > > > > > > Also the table has a primary key. > > > > > > > Rgds > > > > > > > JFB > > > > > > > > > > > > > > > > > '********************************************************************** > > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > > Function Main() > > > > > > > 'Declare Variables > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > > ",") > > > > > > > DestinationArray = > > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > > ",") > > > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > > For Each Field In SourceArray > > > > > > > If Not IsNull(Field) then > > > > > > > 'MsgBox Field > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > > End if > > > > > > > count = count + 1 > > > > > > > next > > > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > > I would do it like this > > > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > > 2. Create a new transformation object for each source column > > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > Hi All, > > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > > I can collect all the source columns in a string, when I call > > > the > > > > > pkg I > > > > > > > pass > > > > > > > > > this string as GV. > > > > > > > > > In my Transformation I want to change change the name of the > > > source > > > > > > > columns. > > > > > > > > > How can I do this ? > > > > > > > > > Tks > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > >
Done
[quoted text, click to view] "Juliane" <Juliane@discussions.microsoft.com> wrote in message
news:Juliane@discussions.microsoft.com: > Hi Allan, > > i have a similar problem: Importing text files and do not know in advance > how many columns the files have. Can you send me your example too ? THANS so > much ! > > JulianeB@Hotmail.com > > Juliane > > "Allan Mitchell" wrote: > > > It is relatively easy to do. > > > > The best way would be to design a package in designer that does what you > > want and then save as a VB module. Have a look in there at how DTS > > itself does it. > > > > Allan > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > Allan, > > > Tks for the example... now I have another problem. > > > Sometimes we want to use the same source column in two diferent destination > > > columns. > > > I'm getting an error saying the Tranformatio_columName already exist. > > > How can I fix that? > > > Rgds > > > JFB > > > > > > "JFB" <help@jfb.com> wrote in message > > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > > > Tks Allan, > > > > I really appreciate you help. j.balladares@verizon.net > > > > Rgds > > > > JFB > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > > > Throw me a mail and I'll post the package > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > Do you remember the posted subject name? > > > > > > Otherwise I will keep searching for it > > > > > > Tks > > > > > > JFB > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > > > Yes you can do it dynamagically. > > > > > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > > > took > > > > a > > > > > > > Query, Parsed it, created a table in Excel and the created the > > > > mappings. > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > > > Can i do this steps dynamicly? > > > > > > > > I have this for script on the transformation, ir works if I run > > > the > > > > > > package > > > > > > > > manually, but If I change the source file the source columns > > > remain > > > > > > there. > > > > > > > > Also the table has a primary key. > > > > > > > > Rgds > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > '********************************************************************** > > > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > > > Function Main() > > > > > > > > 'Declare Variables > > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > > > ",") > > > > > > > > DestinationArray = > > > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > > > ",") > > > > > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > > > For Each Field In SourceArray > > > > > > > > If Not IsNull(Field) then > > > > > > > > 'MsgBox Field > > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > > > End if > > > > > > > > count = count + 1 > > > > > > > > next > > > > > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > > > I would do it like this > > > > > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > > > 2. Create a new transformation object for each source column > > > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > Hi All, > > > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > > > I can collect all the source columns in a string, when I call > > > > the > > > > > > pkg I > > > > > > > > pass > > > > > > > > > > this string as GV. > > > > > > > > > > In my Transformation I want to change change the name of the > > > > source > > > > > > > > columns. > > > > > > > > > > How can I do this ? > > > > > > > > > > Tks > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Alan
Please send me a copy. I've been looking for this solution for a long time. Regards Peter [quoted text, click to view] "Allan Mitchell" wrote:
> Done > > "Juliane" <Juliane@discussions.microsoft.com> wrote in message > news:Juliane@discussions.microsoft.com: > > > Hi Allan, > > > > i have a similar problem: Importing text files and do not know in advance > > how many columns the files have. Can you send me your example too ? THANS so > > much ! > > > > JulianeB@Hotmail.com > > > > Juliane > > > > "Allan Mitchell" wrote: > > > > > It is relatively easy to do. > > > > > > The best way would be to design a package in designer that does what you > > > want and then save as a VB module. Have a look in there at how DTS > > > itself does it. > > > > > > Allan > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > Allan, > > > > Tks for the example... now I have another problem. > > > > Sometimes we want to use the same source column in two diferent destination > > > > columns. > > > > I'm getting an error saying the Tranformatio_columName already exist. > > > > How can I fix that? > > > > Rgds > > > > JFB > > > > > > > > "JFB" <help@jfb.com> wrote in message > > > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > > > > Tks Allan, > > > > > I really appreciate you help. j.balladares@verizon.net > > > > > Rgds > > > > > JFB > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > > > > Throw me a mail and I'll post the package > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > Do you remember the posted subject name? > > > > > > > Otherwise I will keep searching for it > > > > > > > Tks > > > > > > > JFB > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > > > > Yes you can do it dynamagically. > > > > > > > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > > > > took > > > > > a > > > > > > > > Query, Parsed it, created a table in Excel and the created the > > > > > mappings. > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > > > > Can i do this steps dynamicly? > > > > > > > > > I have this for script on the transformation, ir works if I run > > > > the > > > > > > > package > > > > > > > > > manually, but If I change the source file the source columns > > > > remain > > > > > > > there. > > > > > > > > > Also the table has a primary key. > > > > > > > > > Rgds > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > '********************************************************************** > > > > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > > > > Function Main() > > > > > > > > > 'Declare Variables > > > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > > > > ",") > > > > > > > > > DestinationArray = > > > > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > > > > ",") > > > > > > > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > > > > For Each Field In SourceArray > > > > > > > > > If Not IsNull(Field) then > > > > > > > > > 'MsgBox Field > > > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > > > > End if > > > > > > > > > count = count + 1 > > > > > > > > > next > > > > > > > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > > > > I would do it like this > > > > > > > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > > > > 2. Create a new transformation object for each source column > > > > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > > > Hi All, > > > > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > > > > I can collect all the source columns in a string, when I call > > > > > the > > > > > > > pkg I > > > > > > > > > pass > > > > > > > > > > > this string as GV. > > > > > > > > > > > In my Transformation I want to change change the name of the > > > > > source > > > > > > > > > columns. > > > > > > > > > > > How can I do this ? > > > > > > > > > > > Tks > > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Alan sorry my email is avenants@gmail.com
Thanks Peter [quoted text, click to view] "Peter" wrote:
> Alan > > Please send me a copy. I've been looking for this solution for a long time. > > Regards > Peter > > "Allan Mitchell" wrote: > > > Done > > > > "Juliane" <Juliane@discussions.microsoft.com> wrote in message > > news:Juliane@discussions.microsoft.com: > > > > > Hi Allan, > > > > > > i have a similar problem: Importing text files and do not know in advance > > > how many columns the files have. Can you send me your example too ? THANS so > > > much ! > > > > > > JulianeB@Hotmail.com > > > > > > Juliane > > > > > > "Allan Mitchell" wrote: > > > > > > > It is relatively easy to do. > > > > > > > > The best way would be to design a package in designer that does what you > > > > want and then save as a VB module. Have a look in there at how DTS > > > > itself does it. > > > > > > > > Allan > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > Allan, > > > > > Tks for the example... now I have another problem. > > > > > Sometimes we want to use the same source column in two diferent destination > > > > > columns. > > > > > I'm getting an error saying the Tranformatio_columName already exist. > > > > > How can I fix that? > > > > > Rgds > > > > > JFB > > > > > > > > > > "JFB" <help@jfb.com> wrote in message > > > > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > > > > > Tks Allan, > > > > > > I really appreciate you help. j.balladares@verizon.net > > > > > > Rgds > > > > > > JFB > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > > > > > Throw me a mail and I'll post the package > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > Do you remember the posted subject name? > > > > > > > > Otherwise I will keep searching for it > > > > > > > > Tks > > > > > > > > JFB > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > > > > > Yes you can do it dynamagically. > > > > > > > > > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > > > > > took > > > > > > a > > > > > > > > > Query, Parsed it, created a table in Excel and the created the > > > > > > mappings. > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > > > > > Can i do this steps dynamicly? > > > > > > > > > > I have this for script on the transformation, ir works if I run > > > > > the > > > > > > > > package > > > > > > > > > > manually, but If I change the source file the source columns > > > > > remain > > > > > > > > there. > > > > > > > > > > Also the table has a primary key. > > > > > > > > > > Rgds > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > '********************************************************************** > > > > > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > > > > > Function Main() > > > > > > > > > > 'Declare Variables > > > > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > > > > > ",") > > > > > > > > > > DestinationArray = > > > > > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > > > > > ",") > > > > > > > > > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > > > > > For Each Field In SourceArray > > > > > > > > > > If Not IsNull(Field) then > > > > > > > > > > 'MsgBox Field > > > > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > > > > > End if > > > > > > > > > > count = count + 1 > > > > > > > > > > next > > > > > > > > > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > > > > > I would do it like this > > > > > > > > > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > > > > > 2. Create a new transformation object for each source column > > > > > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > > > > > Hi All, > > > > > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > > > > > I can collect all the source columns in a string, when I call > > > > > > the > > > > > > > > pkg I > > > > > > > > > > pass > > > > > > > > > > > > this string as GV. > > > > > > > > > > > > In my Transformation I want to change change the name of the > > > > > > source > > > > > > > > > > columns. > > > > > > > > > > > > How can I do this ? > > > > > > > > > > > > Tks > > > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Juliane has taken care of this for me.
Thanks Juliane Allan [quoted text, click to view] "Peter" <Peter@discussions.microsoft.com> wrote in message
news:Peter@discussions.microsoft.com: > Alan sorry my email is avenants@gmail.com > > Thanks > Peter > > "Peter" wrote: > > > Alan > > > > Please send me a copy. I've been looking for this solution for a long time. > > > > Regards > > Peter > > > > "Allan Mitchell" wrote: > > > > > Done > > > > > > "Juliane" <Juliane@discussions.microsoft.com> wrote in message > > > news:Juliane@discussions.microsoft.com: > > > > > > > Hi Allan, > > > > > > > > i have a similar problem: Importing text files and do not know in advance > > > > how many columns the files have. Can you send me your example too ? THANS so > > > > much ! > > > > > > > > JulianeB@Hotmail.com > > > > > > > > Juliane > > > > > > > > "Allan Mitchell" wrote: > > > > > > > > > It is relatively easy to do. > > > > > > > > > > The best way would be to design a package in designer that does what you > > > > > want and then save as a VB module. Have a look in there at how DTS > > > > > itself does it. > > > > > > > > > > Allan > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > Allan, > > > > > > Tks for the example... now I have another problem. > > > > > > Sometimes we want to use the same source column in two diferent destination > > > > > > columns. > > > > > > I'm getting an error saying the Tranformatio_columName already exist. > > > > > > How can I fix that? > > > > > > Rgds > > > > > > JFB > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message > > > > > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > > > > > > Tks Allan, > > > > > > > I really appreciate you help. j.balladares@verizon.net > > > > > > > Rgds > > > > > > > JFB > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > > > > > > Throw me a mail and I'll post the package > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > Do you remember the posted subject name? > > > > > > > > > Otherwise I will keep searching for it > > > > > > > > > Tks > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > > > > > > Yes you can do it dynamagically. > > > > > > > > > > > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > > > > > > took > > > > > > > a > > > > > > > > > > Query, Parsed it, created a table in Excel and the created the > > > > > > > mappings. > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > > > > > > Can i do this steps dynamicly? > > > > > > > > > > > I have this for script on the transformation, ir works if I run > > > > > > the > > > > > > > > > package > > > > > > > > > > > manually, but If I change the source file the source columns > > > > > > remain > > > > > > > > > there. > > > > > > > > > > > Also the table has a primary key. > > > > > > > > > > > Rgds > > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > > > > '********************************************************************** > > > > > > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > > > > > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > > > > > > Function Main() > > > > > > > > > > > 'Declare Variables > > > > > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > > > > > > ",") > > > > > > > > > > > DestinationArray = > > > > > > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > > > > > > ",") > > > > > > > > > > > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > > > > > > For Each Field In SourceArray > > > > > > > > > > > If Not IsNull(Field) then > > > > > > > > > > > 'MsgBox Field > > > > > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > > > > > > End if > > > > > > > > > > > count = count + 1 > > > > > > > > > > > next > > > > > > > > > > > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > > > > > > I would do it like this > > > > > > > > > > > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > > > > > > 2. Create a new transformation object for each source column > > > > > > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > > > > > > > Hi All, > > > > > > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > > > > > > I can collect all the source columns in a string, when I call > > > > > > > the > > > > > > > > > pkg I > > > > > > > > > > > pass > > > > > > > > > > > > > this string as GV. > > > > > > > > > > > > > In my Transformation I want to change change the name of the > > > > > > > source > > > > > > > > > > > columns. > > > > > > > > > > > > > How can I do this ? > > > > > > > > > > > > > Tks > > > > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Hi Allan, I am looking for this solution for a long time, can you post the code. Thanks, Lak
The code is inside a package so ping me by eMail and I'll send it along
[quoted text, click to view] "Lak Mal" <lmalisetty@yahoo.com> wrote in message
news:lmalisetty@yahoo.com: > Hi Allan, > > I am looking for this solution for a long time, can you post the code. > > Thanks, > Lak > > *** Sent via Developersdex http://www.developersdex.com ***
done
-- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - SQL Server 2005 Integration Services. www.Konesans.com [quoted text, click to view] "Yuliaan" <Yuliaan@discussions.microsoft.com> wrote in message news:D3A1220D-E95A-41A2-AB5C-6F4B5A376E94@microsoft.com...
> Alan > > I've encountered the same problem. Could you send me a copy too? > My mail: olegrom@012.net.il > > Regards > Yuliaan > > > "Allan Mitchell" wrote: > >> Juliane has taken care of this for me. >> >> Thanks Juliane >> >> Allan >> >> "Peter" <Peter@discussions.microsoft.com> wrote in message >> news:Peter@discussions.microsoft.com: >> >> > Alan sorry my email is avenants@gmail.com >> > >> > Thanks >> > Peter >> > >> > "Peter" wrote: >> > >> > > Alan >> > > >> > > Please send me a copy. I've been looking for this solution for a long time. >> > > >> > > Regards >> > > Peter >> > > >> > > "Allan Mitchell" wrote: >> > > >> > > > Done >> > > > >> > > > "Juliane" <Juliane@discussions.microsoft.com> wrote in message >> > > > news:Juliane@discussions.microsoft.com: >> > > > >> > > > > Hi Allan, >> > > > > >> > > > > i have a similar problem: Importing text files and do not know in advance >> > > > > how many columns the files have. Can you send me your example too ? THANS so >> > > > > much ! >> > > > > >> > > > > JulianeB@Hotmail.com >> > > > > >> > > > > Juliane >> > > > > >> > > > > "Allan Mitchell" wrote: >> > > > > >> > > > > > It is relatively easy to do. >> > > > > > >> > > > > > The best way would be to design a package in designer that does what you >> > > > > > want and then save as a VB module. Have a look in there at how DTS >> > > > > > itself does it. >> > > > > > >> > > > > > Allan >> > > > > > >> > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: >> > > > > > >> > > > > > > Allan, >> > > > > > > Tks for the example... now I have another problem. >> > > > > > > Sometimes we want to use the same source column in two diferent destination >> > > > > > > columns. >> > > > > > > I'm getting an error saying the Tranformatio_columName already exist. >> > > > > > > How can I fix that? >> > > > > > > Rgds >> > > > > > > JFB >> > > > > > > >> > > > > > > "JFB" <help@jfb.com> wrote in message >> > > > > > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... >> > > > > > > > Tks Allan, >> > > > > > > > I really appreciate you help. j.balladares@verizon.net >> > > > > > > > Rgds >> > > > > > > > JFB >> > > > > > > > >> > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message >> > > > > > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... >> > > > > > > > > Throw me a mail and I'll post the package >> > > > > > > > > >> > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: >> > > > > > > > > >> > > > > > > > > > Do you remember the posted subject name? >> > > > > > > > > > Otherwise I will keep searching for it >> > > > > > > > > > Tks >> > > > > > > > > > JFB >> > > > > > > > > > >> > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message >> > > > > > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... >> > > > > > > > > > > Yes you can do it dynamagically. >> > > > > > > > > > > >> > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that >> > > > > > > took >> > > > > > > > a >> > > > > > > > > > > Query, Parsed it, created a table in Excel and the created the >> > > > > > > > mappings. >> > > > > > > > > > > >> > > > > > > > > > > Allan >> > > > > > > > > > > >> > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: >> > > > > > > > > > > >> > > > > > > > > > > > Tks for you reply and help Allan, >> > > > > > > > > > > > Can i do this steps dynamicly? >> > > > > > > > > > > > I have this for script on the transformation, ir works if I run >> > > > > > > the >> > > > > > > > > > package >> > > > > > > > > > > > manually, but If I change the source file the source columns >> > > > > > > remain >> > > > > > > > > > there. >> > > > > > > > > > > > Also the table has a primary key. >> > > > > > > > > > > > Rgds >> > > > > > > > > > > > JFB >> > > > > > > > > > > > >> > > > > > > > > > > > >> > > > > > > > '********************************************************************** >> > > > > > > > > > > > ' Visual Basic Transformation Script >> > > > > > > > > > > > >> > > > > > > > > > >> > > > > > > > '************************************************************************ >> > > > > > > > > > > > >> > > > > > > > > > > > ' Copy each source column to the destination column >> > > > > > > > > > > > Function Main() >> > > > > > > > > > > > 'Declare Variables >> > > > > > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field >> > > > > > > > > > > > >> > > > > > > > > > > > 'Get Field values from GVs arrays >> > > > > > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, >> > > > > > > ",") >> > > > > > > > > > > > DestinationArray = >> > > > > > > > Split(DTSGlobalVariables("DestinationFields").Value, >> > > > > > > > > > > > ",") >> > > > > > > > > > > > >> > > > > > > > > > > > count = 0 'Initialize counter >> > > > > > > > > > > > >> > > > > > > > > > > > 'Loop thru array and import columns to table >> > > > > > > > > > > > For Each Field In SourceArray >> > > > > > > > > > > > If Not IsNull(Field) then >> > > > > > > > > > > > 'MsgBox Field >> > > > > > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) >> > > > > > > > > > > > End if >> > > > > > > > > > > > count = count + 1 >> > > > > > > > > > > > next >> > > > > > > > > > > > >> > > > > > > > > > > > Main = DTSTransformStat_OK >> > > > > > > > > > > > End Function >> > > > > > > > > > > > >> > > > > > > > > > > > >> > > > > > > > > > > > >> > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message >> > > > > > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... >> > > > > > > > > > > > > I would do it like this >> > > > > > > > > > > > > >> > > > > > > > > > > > > 1. Remove existing transforms >> > > > > > > > > > > > > 2. Create a new transformation object for each source column >> > > > > > > > > > > > > 3. Add the SourceColumn to the Transformation >> > > > > > > > > > > > > 4. Add a DestinationColumn to the transformation >> > > > > > > > > > > > > 5. Add the transformation to the transformations collection >> > > > > > > > > > > > > >> > > > > > > > > > > > > >> > > > > > > > > > > > > Allan >> > > > > > > > > > > > > >> > > > > > > > > > > > > >> > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: >> > > > > > > > > > > > > >> > > > > > > > > > > > > > Hi All, >> > > > > > > > > > > > > > Every time I importing a text file the columns name change.
Alan
I've encountered the same problem. Could you send me a copy too? My mail: olegrom@012.net.il Regards Yuliaan [quoted text, click to view] "Allan Mitchell" wrote:
> Juliane has taken care of this for me. > > Thanks Juliane > > Allan > > "Peter" <Peter@discussions.microsoft.com> wrote in message > news:Peter@discussions.microsoft.com: > > > Alan sorry my email is avenants@gmail.com > > > > Thanks > > Peter > > > > "Peter" wrote: > > > > > Alan > > > > > > Please send me a copy. I've been looking for this solution for a long time. > > > > > > Regards > > > Peter > > > > > > "Allan Mitchell" wrote: > > > > > > > Done > > > > > > > > "Juliane" <Juliane@discussions.microsoft.com> wrote in message > > > > news:Juliane@discussions.microsoft.com: > > > > > > > > > Hi Allan, > > > > > > > > > > i have a similar problem: Importing text files and do not know in advance > > > > > how many columns the files have. Can you send me your example too ? THANS so > > > > > much ! > > > > > > > > > > JulianeB@Hotmail.com > > > > > > > > > > Juliane > > > > > > > > > > "Allan Mitchell" wrote: > > > > > > > > > > > It is relatively easy to do. > > > > > > > > > > > > The best way would be to design a package in designer that does what you > > > > > > want and then save as a VB module. Have a look in there at how DTS > > > > > > itself does it. > > > > > > > > > > > > Allan > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > Allan, > > > > > > > Tks for the example... now I have another problem. > > > > > > > Sometimes we want to use the same source column in two diferent destination > > > > > > > columns. > > > > > > > I'm getting an error saying the Tranformatio_columName already exist. > > > > > > > How can I fix that? > > > > > > > Rgds > > > > > > > JFB > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message > > > > > > > news:O2ilPnNKFHA.3296@TK2MSFTNGP15.phx.gbl... > > > > > > > > Tks Allan, > > > > > > > > I really appreciate you help. j.balladares@verizon.net > > > > > > > > Rgds > > > > > > > > JFB > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > news:#cusrfNKFHA.3076@tk2msftngp13.phx.gbl... > > > > > > > > > Throw me a mail and I'll post the package > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > Do you remember the posted subject name? > > > > > > > > > > Otherwise I will keep searching for it > > > > > > > > > > Tks > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > > > news:OgIdkTNKFHA.1096@tk2msftngp13.phx.gbl... > > > > > > > > > > > Yes you can do it dynamagically. > > > > > > > > > > > > > > > > > > > > > > I have posted before about a script I wrote in ActiveX task that > > > > > > > took > > > > > > > > a > > > > > > > > > > > Query, Parsed it, created a table in Excel and the created the > > > > > > > > mappings. > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > > > > > Tks for you reply and help Allan, > > > > > > > > > > > > Can i do this steps dynamicly? > > > > > > > > > > > > I have this for script on the transformation, ir works if I run > > > > > > > the > > > > > > > > > > package > > > > > > > > > > > > manually, but If I change the source file the source columns > > > > > > > remain > > > > > > > > > > there. > > > > > > > > > > > > Also the table has a primary key. > > > > > > > > > > > > Rgds > > > > > > > > > > > > JFB > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > '********************************************************************** > > > > > > > > > > > > ' Visual Basic Transformation Script > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > '************************************************************************ > > > > > > > > > > > > > > > > > > > > > > > > ' Copy each source column to the destination column > > > > > > > > > > > > Function Main() > > > > > > > > > > > > 'Declare Variables > > > > > > > > > > > > Dim SourceArray, DestiantionArray, count, temp, Field > > > > > > > > > > > > > > > > > > > > > > > > 'Get Field values from GVs arrays > > > > > > > > > > > > SourceArray = Split(DTSGlobalVariables("SourceFields").Value, > > > > > > > ",") > > > > > > > > > > > > DestinationArray = > > > > > > > > Split(DTSGlobalVariables("DestinationFields").Value, > > > > > > > > > > > > ",") > > > > > > > > > > > > > > > > > > > > > > > > count = 0 'Initialize counter > > > > > > > > > > > > > > > > > > > > > > > > 'Loop thru array and import columns to table > > > > > > > > > > > > For Each Field In SourceArray > > > > > > > > > > > > If Not IsNull(Field) then > > > > > > > > > > > > 'MsgBox Field > > > > > > > > > > > > DTSDestination(DestinationArray(count)) = DTSSource(Field) > > > > > > > > > > > > End if > > > > > > > > > > > > count = count + 1 > > > > > > > > > > > > next > > > > > > > > > > > > > > > > > > > > > > > > Main = DTSTransformStat_OK > > > > > > > > > > > > End Function > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > > > > > > > > > > news:ORM#wMNKFHA.3132@TK2MSFTNGP12.phx.gbl... > > > > > > > > > > > > > I would do it like this > > > > > > > > > > > > > > > > > > > > > > > > > > 1. Remove existing transforms > > > > > > > > > > > > > 2. Create a new transformation object for each source column > > > > > > > > > > > > > 3. Add the SourceColumn to the Transformation > > > > > > > > > > > > > 4. Add a DestinationColumn to the transformation > > > > > > > > > > > > > 5. Add the transformation to the transformations collection > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Allan > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "JFB" <help@jfb.com> wrote in message news:help@jfb.com: > > > > > > > > > > > > > > > > > > > > > > > > > > > Hi All, > > > > > > > > > > > > > > Every time I importing a text file the columns name change. > > > > > > > > > > > > > > I can collect all the source columns in a string, when I call > > > > > > > > the > > > > > > > > > > pkg I > > > > > > > > > > > > pass > > > > > > > > > > > > > > this string as GV. > > > > > > > > > > > > > > In my Transformation I want to change change the name of the > > > > > > > > source > > > > > > > > > > > > columns. > > > > > > > > > > > > > > How can I do this ? > > > > > > > > > > > > > > Tks > > > > > > > > > > > > > > JFB > > > > > > > > > > > > >
On its way
[quoted text, click to view] "Shannon Winterton" <gitsupport@hotmail.com> wrote in message
news:gitsupport@hotmail.com: > Alan, > Dito, could you also please send to me as well. > gitsupport@hotmail.com > Shannon > > *** Sent via Developersdex http://www.developersdex.com ***
Alan, Dito, could you also please send to me as well. gitsupport@hotmail.com Shannon
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |