Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Help with a VB Transformation Script

I am needing to write a VB script that will take a table transform it and then copy the table to another new table.  I am using the SQL Server Manager and using the Emport table wizard.  When I choose a destination table I then can select the transformations tab to run vb script before sending the data to a new table.  I have never used vb script, I have been using vb.net recently for different apps, but not specifically vb script.  Basically i have a table with two columns.  One colum is storing an order number and the other is storing the source code for a particular catalog we send out to customers.  Well the column storing the source code can store up to 8 different source codes.  Each source code is being delimited by a *|*.  So basically if I have three source codes it would look like this......
3456*|*6789*|*1234

I need to parse out each of those source codes and place each one into its own seperate column.  So I could have up to 9 total columns in the table (1 for order # and 8 for source codes).  This is why I am using the export data wizard to export the table to another newly created table, but a transform the data before sending it to the new table.  Can someone help me with the vb script code necessary for this job??  Here is the default code inside the transformation tab.....

'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************

Function Main()
      DTSDestination("cw_number") = DTSSource("cw_number")
      DTSDestination("source_Codes") = DTSSource("source_Codes")
      Main = DTSTransformStat_OK
End Function


If somone could help me with the coding that would be great.  Thanks for the help.  
0
icarus2256
Asked:
icarus2256
  • 11
  • 8
1 Solution
 
MichaelSFullerCommented:
Is this what your looking for?

Function Main()

'declare variables
Dim myarray

'use the split funtion to populate my array
myarray = Split(DTSSource("source_Codes"), "#")

'transfom each row
'array looks like this in memory...
'Position Value
'    0       3456
'    1       |
'    2       6789
'    3       |
'    4       1234

DTSDestination("cw_number") = DTSSource("cw_number")
DTSDestination("column 1") = myarray(0)
DTSDestination("column 2") = myarray(2)
DTSDestination("column 3") = myarray(4)

Main = DTSTransformStat_OK
End Function



0
 
MichaelSFullerCommented:
Opps what the heck was I thinking, i wrote it without thinking clearly I meant

Function Main()

'declare variables
Dim myarray

'use the split funtion to populate my array
myarray = Split(DTSSource("source_Codes"), "*|*")

'transfom each row
'array looks like this in memory...
'Position Value
'    0       3456
'    1       6789
'    2       1234

DTSDestination("cw_number") = DTSSource("cw_number")
DTSDestination("column 1") = myarray(0)
DTSDestination("column 2") = myarray(1)
DTSDestination("column 3") = myarray(2)

Main = DTSTransformStat_OK
End Function
0
 
icarus2256Author Commented:
I am going to need to place each source code number into a new column up to eight columns.  Shouldn't I use a while loop or something like that to check the end of the string??  If so could you help.  Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MichaelSFullerCommented:
No need to do that, the split will take the source code field for each record and break it into an array by delimiter.

for example as you said if you had 3 values (3456*|*6789*|*1234) delimited by "*|*", the split function will break it into an array of 3, (0-2) if you had 4 values the postions within the array would be (0-3).

For example:
Position 0 in the array would contain value 3456 myarray(0) would return the value
Position 1 in the array would contain value 6789 myarray(1) would return the value
Position 1 in the array would contain value 1234 myarray(2) would return the value

If you do not know how many values there are then you would first check it by using the ubound funtion which will return the upper bound of the array-this is the highest element.

As Lbound function will return the first element

For the example above UBound(myArray) would return 2, with that said all you have to do for each row that is being processed is put each value in the correct destination field as I answered above.

Now to check if or if not to put the value in the destination field for the data pump, all you would have to do is a little boolean logic

Dim intUbound 'holds the upper bound of the array

'in our example this would return 2, this because arrays are zero based
intUbound= Ubound(myarray)

If intUbound >=0 Then
   DTSDestination("column 1") = myarray(0)
End if

If intUbound >=1 Then
   DTSDestination("column 2") = myarray(1)
End if
...

For each row it will perform this logic, so you are already in a loop. That's why there is no need for one.



0
 
icarus2256Author Commented:
Here is the code I input:

Function Main()
                Dim myArray
      Dim intUBound
      myArray = split(DTSSource("source_Codes"), "*|*")

      DTSDestination("cw_number") = DTSSource("cw_number")

      intUBound = UBound(myArray)

      if intUBound >= 0 then
            DTSDestination("column 1") = myArray(0)
      end if
      
      if intUBound >= 1 then
            DTSDestination("column 2") = myArray(1)
      end if

      if intUBound >= 2 then
            DTSDestination("column 3") = myArray(2)
      end if

      if intUBound >= 3 then
            DTSDestination("column 4") = myArray(3)
      end if

      if intUBound >= 4 then
            DTSDestination("column 5") = myArray(4)
      end if

      if intUBound >= 5 then
            DTSDestination("column 6") = myArray(5)
      end if

      if intUBound >= 6 then
            DTSDestination("column 7") = myArray(6)
      end if

      if intUBound >= 7 then
            DTSDestination("column 8") = myArray(7)
      end if

      Main = DTSTransformStat_OK

End Function


the code printed above is throwing an error when I actually start the process here is the error message I receive.  

Error during transformation 'AxScriptXForm' for row number 5.  Errors encountered so far in this task: 1
Error code: 0
Error Source = Microsoft VBscript runtime error
Error Description = Invalid procedure call or argument: 'DTSDestination'

Error on line 16
0
 
MichaelSFullerCommented:
Can you give me your table scripts so I can reproduce it?
0
 
icarus2256Author Commented:
I am sorry, but I must be confused.  Can I export the table scripts??  
0
 
MichaelSFullerCommented:
I see it try this, also if you do get and error double check your the Souce columns and Destination columns included in the Active X Script in the Transform Data Task.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()

     Dim myArray
     Dim intUBound

    strSourceCode = DTSSource("source_Codes")

     myArray = split(strSourceCode, "*|*")

     DTSDestination("cw_number") =  DTSSource("cw_number")

     intUBound = UBound(myArray)

     if intUBound >= 0 then
          DTSDestination("column 1") = myArray(0)
     end if
     
     if intUBound >= 1 then
          DTSDestination("column 2") = myArray(1)
     end if

     if intUBound >= 2 then
          DTSDestination("column 3") = myArray(2)
     end if

     if intUBound >= 3 then
          DTSDestination("column 4") = myArray(3)
     end if

     if intUBound >= 4 then
          DTSDestination("column 5") = myArray(4)
     end if

     if intUBound >= 5 then
          DTSDestination("column 6") = myArray(5)
     end if

     if intUBound >= 6 then
          DTSDestination("column 7") = myArray(6)
     end if

     if intUBound >= 7 then
          DTSDestination("column 8") = myArray(7)
     end if

     Main = DTSTransformStat_OK

End Function



0
 
MichaelSFullerCommented:
Did this work or not?
0
 
icarus2256Author Commented:
Sorry for the delay was out for the last couple of days.  I tried the latest code you send, but still receiving error.  I know the destination table is good because I used the defaults included in the the transform script and it worked fine.  Any other ideas???  
0
 
MichaelSFullerCommented:
1)Select Transformations-->Edit the Active X script -Note you should only have one transformation 2 connections from the source grouped together pointing to 9 destination columns  

2)Next select Source are the fields listed as below:
   source_codes
   cw_number

3)Select Destination are the fields listed as below
   cw_number
   column 1
   column 2
   column 3
   column 4
   column 5
   column 6
   column 7
   column 8

If your values are not the same then that would be the reason for the error, otherwise what does your exception file say? It could be a bad row, check row number 5.


0
 
icarus2256Author Commented:
I have a question with the source destination.  This database I am sending the data to is not created already.  I am creating the database during the process.  Basically when i try to assign a source field to different destination field it won't allow it.  The source field and destination fields are the same.  I can't say "send source source_codes to column 1".  Can you clarify where I can set that up because in the DTS export i can only send the source to the same destination.  THanks
0
 
MichaelSFullerCommented:
Here is an example of what I wrote.

The tables reside in Northwind, here's there script:
1) Open up Query Analyzer
2) Choose windows authentication to use your current windows credentials or select SQL and type in your username and password
3) From the drop down list on the toolbar select Northwind
4) Paste the code below and hit F5 or click the run button on the toolbar (green play button)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDestination]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDestination]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSource]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSource]
GO

CREATE TABLE [dbo].[tblDestination] (
      [cw_Number] [int] NOT NULL ,
      [Column 1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 6] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 7] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Column 8] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSource] (
      [cw_number] [int] IDENTITY (1, 1) NOT NULL ,
      [source_codes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Sample test data
insert into tblSource Values('56*|*6789*|*124')
insert into tblSource Values('36*|*679*|*134')
insert into tblSource Values('56*|*89*|*134')
insert into tblSource Values('356*|*69*|*123')
insert into tblSource Values('34*|*69*|*34')
insert into tblSource Values('3456*|*89*|*124')
insert into tblSource Values('46*|*689*|*134')
insert into tblSource Values('356*|*678*|*14')
insert into tblSource Values('36*|*679*|*24')

*************************************************************************
Now to create the package:

To create the source:
1) Right Click EM expand Data Transformation Services
2) Select Local Packages-->Right Click Choose New Package
3) Under Connections select OLE DB Provider for SQL Server
4) Connection Properties will be open now, type in Source for the name of the connection
5) In the Server drop down list select your server's name
6) Choose windows authentication to use your current windows credentials or select SQL and type in your username and password
7) Select the database you will be working with for this example use "Northwind"

 
To create the destination:
1) Under Connections select OLE DB Provider for SQL Server
2) Connection Properties will be open now, type in Destination for the name of the connection
3) In the Server drop down list select your server's name
4) Choose windows authentication to use your current windows credentials or select SQL and type in your username and password
5) Select the database you will be working with for this example use "Northwind" again

To create the Transform Data Task
1)  Under Task select Transform Data Task
2)  Select Source (which you created earlier) as the Source and Destination (which you created earlier) as the Destination
3)  Double click the Transform Data Task you just created to open up its properties
4)  On the first tab Source select Northwind.dbo.Source
5)  On the second tab Destination select Northwind.dbo.Destination
6)  On the third tab Transformations click the delete all button
7)  On the same tab click the select all button
8)  Click the New button
9)  Select Active X script
10) Click OK
11) You now are at the General tab of Transformation Options, select properties
12) Select All and then delete
13) Paste the following script:

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()

     Dim myArray
     Dim intUBound

    strSourceCode = DTSSource("source_codes")

     myArray = split(strSourceCode, "*|*")

     DTSDestination("cw_number") =  DTSSource("cw_number")

     intUBound = UBound(myArray)

     if intUBound >= 0 then
          DTSDestination("column 1") = myArray(0)
     end if
     
     if intUBound >= 1 then
          DTSDestination("column 2") = myArray(1)
     end if

     if intUBound >= 2 then
          DTSDestination("column 3") = myArray(2)
     end if

     if intUBound >= 3 then
          DTSDestination("column 4") = myArray(3)
     end if

     if intUBound >= 4 then
          DTSDestination("column 5") = myArray(4)
     end if

     if intUBound >= 5 then
          DTSDestination("column 6") = myArray(5)
     end if

     if intUBound >= 6 then
          DTSDestination("column 7") = myArray(6)
     end if

     if intUBound >= 7 then
          DTSDestination("column 8") = myArray(7)
     end if

     Main = DTSTransformStat_OK

End Function


14) Choose OK
15) Next select the Source Columns tab and make sure the selected columns list has the fields listed as below (If they are not there add them):
      source_codes
      cw_number

16)Now select Destination Columns and make sure the selected columns list has the fields listed as below:
   cw_number
   column 1
   column 2
   column 3
   column 4
   column 5
   column 6
   column 7
   column 8

17) Click OK
18) Select the Options Tab
19) Enter under exceptions "c:\test.txt"
20) Click OK
21) Click Save and name the package what you want

That should do it.

Hope this gets you there...

-Late










 



0
 
icarus2256Author Commented:
Thanks so much for your help.  Everything worked great in the transformation.  One thing however is that a blank item is appearing in the column to the right of the last souce code number.  I wonder if the split() is throwing a blank number after the last source code number it receives.  Any idea on what may be causing that??  Again thanks so much for your help.  Experts exchange has been worth every penny for the membership.
0
 
MichaelSFullerCommented:
I'm not sure I understand your question, is this DTSDestination("column 8")  so your saying column 9 is blank?


0
 
icarus2256Author Commented:
Ok basically if I have source code 1234*|*5678*|*9103

1234 is placed in column1
5678 is placed in column2
9103 is placed in column3

which is what I want, but a blank record is placed in column4. Column5-8 all show <null> which is correct.  I am trying to figure out where the blank
record is coming from.  column1  column2  column3  column4  column5  column6  column7  column8
                                   1234       5678       9103                     <null>     <null>    <null>    <null>

I hope that made sense.  Thanks
0
 
MichaelSFullerCommented:
It could be any of them perhaps this is the case--> 1234*|*5678*|*9103*|**|*.  To verify this you could do a len if ( len(ltrim(rtrim(myArray(3)))))=0 then -1, if you are dealing with numbers you also want to cast the string to the data type or this could throw an error.

Hope this helps...


0
 
icarus2256Author Commented:
Ok, I see what is happening.  The original table is including a *|* on the end of the last source code.  My function is reading that delimiter and thinking another number is coming after that.  Well there is none so it is pulling a blank.  So again, I should look at the len() you posted above to solve the issue??
0
 
MichaelSFullerCommented:
Well if the database is putting a delimiter on every one, just ignore the last memeber of the array.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now