Read an excel source and run the sp's based on headers in ssis

user_12516314 0
Viewed 51

I have an excel worksheet Sheet1$,

Report1 Report2 Report5 Report6
value1 value1 value1 value1
Two value2 value2

I have several SP's in the database and this worksheet has the parameters for the SP's

Based on the header names in the worksheet, we have to pass the parameters and run that sp.

Example:

In this sheet, I have to run report1, report2, report5, report6 where report 3 and report 4 are not there.

I have to run the above-mentioned with the parameters in each column accordingly. (Note: The parameters has to be passed as a string) The result from the SP should be again saved in a worksheet.

Could someone help me with this?

Thanks in advance

etl ssis excel sql-server
1 Answers

I used ADODB.

Sniplet (Function SQL_EXEC...)

Dim B As Boolean
Dim C As ADODB.Command
Dim I As Long
Dim P As ADODB.Parameter
Dim V As Variant

   Set C = New ADODB.Command
   C.ActiveConnection = glob_CONNECT
   C.CommandType = adCmdStoredProc
   C.CommandText = X.SQL_Name
   
   ' bind Inputparameter
   If X.NInParams > 0 Then
      For I = 1 To X.NInParams Step 1
         Set P = C.CreateParameter(X.InParam(I).SQL_Name, _
                                   X.InParam(I).ADODB_Type, _
                                   adParamInput, _
                                   X.InParam(I).SQL_Size, _
                                   X.InParam(I).SQL_Value)
         C.Parameters.Append P
      Next
   End If
   
   ' bind Outputparameter
   If X.NOutParams > 0 Then
      For I = 1 To X.NOutParams Step 1
         Set P = C.CreateParameter(X.OutParam(I).SQL_Name, _
                                   X.OutParam(I).ADODB_Type, _
                                   adParamOutput, _
                                   X.OutParam(I).SQL_Size)
         C.Parameters.Append P
      Next
   End If
   
   ' Execute SP (Errors are possible!)
   On Error GoTo 0
   On Error Resume Next
   C.Execute
   
   ' Error?
   If Err.Number <> 0 Then
      SQL_EXEC = False
      Exit Function
   End If

   ' Fetch Output
   If X.NOutParams > 0 Then
      For I = 1 To X.NOutParams Step 1
         V = VNormExec(C.Parameters(X.OutParam(I).SQL_Name), X.OutParam(I).SQL_Type)
         X.OutParam(I).SQL_Value = V
      Next
   End If
user_159642410
Related