how Can I use VBA Code in SSIS

[Mittle]

Hi

I am trying too build SSIS package for ETL with an access database after migration .

A Collection tool was written that gets the data from excel and transforms / formats , then loads into access.

Ive put here the part of the VBA code used in the tool .

Task at hand:
I need to be able to convert Long text format fields into wider format i.e add 6 rows in a field into 1 long row.

this is what the part of the code in bold is doing 'Assign additional columns from DataSrce' .

Can anyone please advise if this part of code can be rewritten in tsql or which other programming language ? probably for someone that knows VB and SQL Server. help much appreciated

'PROFILED SPEND ///////////////////////////////////////////////////////

'Assign worksheets to variables
Set wks1 = wkb1.Worksheets("ProfiledSpend")
Set Wks2 = wkb2.Worksheets("ProfiledSpend")

'Remove extraneous cells
If Wks2.Range("A1") <> "'Project" Then
    Wks2.Columns("A").Delete
    Wks2.Rows("1:20").Delete
End If

'Assign Data to Arrays

DataTrans = wks0.Range("Cost_Trans").Value
DataSrce = Wks2.Range("A1").CurrentRegion.Value
ReDim Data((UBound(DataSrce, 1) / Bse(5, 6)), 12)                           'The number of Rows /Columns in the Profiled Spend table

'Row number assignment into Data() variable
k = 1

'Loop through data
For i = 1 To UBound(DataSrce, 1) Step 5
  
    ''Debug.Print i, DataSrce(i, 1)
  
    'Assign known data - IDQ, ID and Qtr
    Data(k, 1) = DataSrce(i, 2) & "-" & DataSrce(i, 4)          'IDQ
    Data(k, 2) = DataSrce(i, 2)                                 'ID Number
    Data(k, 3) = DataSrce(i, 4)                                 'Quarter
      
    'Look up and assign spend data types
    For j = 1 To UBound(DataTrans, 1) Step Bse(5, 6)
      
        If DataTrans(j, 1) = DataSrce(i, 5) Then
                
            Data(k, 4) = DataTrans(j, 3)
            Data(k, 5) = DataTrans(j, 4)
            Data(k, 6) = DataTrans(j, 2)
            Exit For
        End If
      
    Next j
  
    'Assign additional columns from DataSrce
  
            Data(k, 7) = DataSrce(i, 8)
            Data(k, 8) = DataSrce((i + 1), 8)
            Data(k, 9) = DataSrce((i + 2), 8)
            Data(k, 10) = DataSrce((i + 3), 8)
            Data(k, 12) = DataSrce((i + 4), 8)
          
            'Sum components for WLC total
            Data(k, 11) = (DataSrce((i), 8) + DataSrce((i + 1), 8) + DataSrce((i + 2), 8))
  
  
    Debug.Print i & ", " & k
    Debug.Print Data(k, 1)
    Debug.Print Data(k, 11), Data(k, 7), Data(k, 8)
  

    k = k + 1

Next i


wks1.Activate

Add1 = wks1.Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1).Address


Add2 = wks1.Cells(((UBound(Data, 1)) + WorksheetFunction.CountA(Range("A:A"))), UBound(Data, 2)).Address

Addrss = Add1 & ":" & Add2

'Debug.Print Addrss, Add1, Add2

wks1.Range(Addrss).Value = Data

'Clear Arrays
Erase DataTrans
Erase DataSrce
Erase Data

 

[Isaac]

I have to preface this by saying, technically speaking, it's absolutely not recommended to use Office automation for an unattended package running on a server.

But, to keep the solution simple and familiar, you can use VBA ( createobject("excel.application") ) inside a Script task. Me personally I set them to vb.net (it's the most like VBA and VB and VBScript, so I'm most familiar with it), but you're free to use c#.net if desired.

The code will be about the same as your current code, except everything will need to be late-bound. (VSTA will bark at you for that--it's not considered good in that environment), but you can do it.

 

[Mittle]

Thanks for the prompt response.

Please elaborate on your advise as am only a DBA and have no idea of VB . ive never done this sort of formatting before . was only asked if this is possible with SSIS otherwise how else do u think this can be done as the excel spreadsheets need this kind of formatting long text fields into wider fields


you said"
"it's absolutely not recommended to use Office automation for an unattended package running on a server."

"its not considered Good in that environment"


if not is there any other way . as they want to stop using this collation tool. and let SQL do the import and formatting . in a nutshell if not possible then I will have to advise the users

Thanks

 

[Isaac]

Question, is the only reason you're using SSIS for this because it came to mind as a good fit due to, you need something that you can schedule to run a job?

Because it might not be the best fit for this.

If the above code is all you need to do to Excel, I'd probably write a VBScript to do it and schedule it using Windows Task Scheduler (wscript.exe as start program, path to .vbs as argument, in quotes)

 

[Mittle]

oh this is only the code for one of the worksheets out of several (30) .its about

but Yes you are correct .their is code written for every worksheet that makes up the Collection Tool which is kind of a repetition of this code posted here but for different Worksheets.

so in a nutshell the collection tool collates all returns into tables of data for loading into the access database. it does all the transformation in VB using Macros.

1.MasteSheet:contains a series of macros that perform the action of the tool
2. Imports
3.16 worksheets for data collection and their format all linked to the access database


and it would be good to replicate this in SSIS if can be done as the goal is to stop using access database


This is a migration project from access to SQL Server
Phase1: of the project which is to migrate tables , Queries into SQL Server - completed


Phase 2: is now to replicate this Collection/collation tool in SQL Server if it can be done . Its not so much of a challenge if such formatting is not required but thats a critical part .so I just need to know if I should go with your suggestion of Windows task Schedular and write VB Script ( well not me as am not a developer OR use SSIS but looks like you are not in favour of SSIS in this instance

summary of what the above code is doing is here .

Dim wkb1 As Workbook            'This workbook
Dim wkb2 As Workbook            ' Non zero suppressed output
Dim wks0 As Worksheet           'The worksheet
Dim wks1 As Worksheet           'The worksheet receiving the data
Dim Wks2 As Worksheet           'The worksheet in the extract providing the data
Dim Bse As Variant              'Array containing data type, Iterations etc for conversion and Yes / No for narrow or Wide report
Dim DataTrans As Variant        'Array for conversion of data
Dim DataFrmat As Variant        'Array to asign data to the correct columns
Dim Data As Variant           'Array of Profiled spend data for adding to collation
Dim DataSrce As Variant         'Array of Source table for conversion
Dim i As Long                   'First loop
Dim j As Integer                'secondary loop
Dim k As Long                   'teriary loop
Dim l As Integer                'Loop through wide tables
Dim Add1 As String
Dim Add2 As String
Dim Addrss As String

 

[Mittle]

The answer to Isaac is No. the tool consist of up to 16 Excel worksheets and the code copied above is only one worksheet . so a lot of code.
and ur correct . the reason why SSIS came to mind is the job scheduling etc


I suppose my question is advise or suggestion on the best solution to replace this Collection Tool since we have migrated tables , Queries to access,

Is SSIS not a best solution. ? the VBA code in bold ? can it only be written in tsql ?sorry if silly question but am not a developer as such so I dont know

any suggestions on the best solution please ?

so my own suggestion is

1.Import the data using SSIS
2.then do the formatting of converting the longer text fields into wider with Script task
3.later updates/inserts to main tables will be written using stored proc