locked
convert VB Script to Script task in SSIS RRS feed

  • Question

  • Hi.

    I have an existing VB Script code , which opens excel and reads the data and then inserts it into SQL table. I have checked the code available in google to insert data from excel to SQL, but would need help to understand the items in the vb script. Below is the VB script code, which I need help to convert to C#. Is this data reading excel file line by line ? How to convert the below to script task code of SSIS ?

    Sub Read_Files
    
    	Dim fso,f1,d1,file,wrk,fcount,ext
    	Set fso = CreateObject("Scripting.FileSystemObject") 
    	Set d1=fso.GetFolder(Dir) 
    	If Err > 0 Then 
    		MsgBox "Error (002): " + Err.Description & vbNewline & vbNewline & _
    		"This application will now terminate!", vbCritical, "Error!"
    		Exit Sub
    	End If
    	
    	Set xl = CreateObject("EXCEL.APPLICATION") 
    	xl.Application.Visible = true
    
    	fcount = 0
    	For each file in d1.files 
    
    		ext = fso.GetExtensionName(file.name)
          
    		If ext = "xls" or ext = "xlsx" Then
    			lineNo = 2
    			Set wrk = xl.Workbooks.Open(file) 
    			If Err > 0 Then 
    				MsgBox "Error (003): " & Err.Description, vbCritical, "Error!"
    				Exit Sub
    			End If
    
    			Call Read_Data
    			wrk.Close			
    			fcount = fcount + 1
    		End If
    	Next 
    			
    	Set wrk = Nothing 
    	xl.Application.Quit
    	Set xl = Nothing 
    	Set d1 = Nothing
    	Set fso = Nothing	
    
    	Msgbox "Number of files processed: " & fcount & vbNewline & vbNewline & _
    	           "Number of records inserted: " & recCount & vbNewline & vbNewline & _
    	           "Number of errors: " & errCount & vbNewline & vbNewline & _
    		   "See next steps.", vbInformation, "Loader - Complete"
    	
    	Err.Clear
    End Sub
    
    Sub Read_Data
    	Dim sql, i, i1, i2, i3
    
    	i = FindHeaderRow() + 1
    	
    	Set adoConnect = CreateObject("ADODB.Connection")
       	adoStr = "Provider=SQLOLEDB; Data Source=server;"
        	adoConnect.ConnectionTimeout = 120
        	adoConnect.Open adoStr
    
    	Do		
    		'Check that the field is not null
    
    			i1 = format(xl.ActiveSheet.Cells(i,1).Value)
    			i2 = format(xl.ActiveSheet.Cells(i,2).Value)
    			i3 = format(xl.ActiveSheet.Cells(i,3).Value)
    			
    		If (i1 = "" OR  i2 = "") and i3 <> "" Then  
    			msgbox "Records skipped as (column A) or (column B) is blank"
    
    			Exit Do
    			'goto NextRecord
    		ElseIf i1 = "" AND  i2 = "" AND i3 = "" Then  			
    			Exit Do
    			'goto NextRecord
    		Else
    		               
                dim f1, f2, f3, f4, f5, f6, f7, f8
                
        	    if len(Format(xl.ActiveSheet.Cells(i,6).Value)) >= 8 then 	            
    
    	            f6 = Format(xl.ActiveSheet.Cells(i,6).Value)
    	            
    	            if DATE_FORMAT_YYYYMMDD =  SniffDateFormat( f6 ) Then
    	            f6 = Mid( f6, 5, 2 ) & "/" & Right( f6, 2 ) & "/" & Left( f6 , 4 )
    	               else
    	               f6 = Month(f6) & "/" & Day(f6) & "/" & Year(f6)
    	            end if
                
    	            f8 = Format(xl.ActiveSheet.Cells(i,8).Value)
                
    	            if DATE_FORMAT_YYYYMMDD =  SniffDateFormat( f8 ) Then
    	           f8 = Mid( f8, 5, 2 ) & "/" & Right( f8, 2 ) & "/" & Left( f8 , 4 )
    	               else
    	               f8 = Month(f8) & "/" & Day(f8) & "/" & Year(f8)
    	            end if            
                f1 = Format( xl.ActiveSheet.Cells(i,1).Value)           
    	    f2 = Format( xl.ActiveSheet.Cells(i,2).Value)
                f3 = Format( xl.ActiveSheet.Cells(i,3).Value)
                f4 = Format( xl.ActiveSheet.Cells(i,4).Value)
                f5 = Format( xl.ActiveSheet.Cells(i,5).Value)
                            
    			sql = "'" & f1 & "'," & _
    			"'" &  f2 & "'," & _
    			"'" &  f3 & "'," & _
    			"'" &  f4 & "'," & _
    			"'" &  f5 & "'," & _
    			"'" &  Now() & "'"
    
    			Insert_Data(sql)
                else
    		msgbox "Record skipped " 
    		End If
    	end if
    	'NextRecord:
    		i = i + 1
    		lineNo = lineNo + 1
    	Loop
    	adoConnect.Close
    	Set adoConnect = Nothing	
    End Sub
    
    
    Function Format(str)
    	str = Replace(str,"'","''")
    	Format = str
    End Function 
    
    
    Function FindHeaderRow()
           dim i
    
    if xl.ActiveSheet.Rows.Count > 65536 then 
    FindHeaderRow = 1 
    Exit Function
    end if
    
        for i=1 to xl.ActiveSheet.Rows.Count
        
            if xl.ActiveSheet.Cells(i,1).Value = "First" or xl.ActiveSheet.Cells(i,2).Value = "Next Sequence" Then
                FindHeaderRow = i
                Exit Function
            end if
            
        next
    
        FindHeaderRow = -99
        
    End Function

    Thanks
    Tuesday, March 24, 2020 1:36 PM

All replies

  • Hi Venkatzeus,

    In Script Task, we can use Microsoft Visual Basic 2017 or Microsoft Visual C#2017 as script language.

    About converting VB to C#,please use a free tool in the following link:

      https://converter.telerik.com/

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, March 25, 2020 7:16 AM
  • Hi.

    Thanks for the reply. The code I have is in VB Script.

    Is there any other way to read the excel file - row by row and then insert the data to SQL ?I cannot do a SQLBulkCopy , as the rows need to be validated before the SQL insertion.

    How to open excel and read line by line and then insert to SQL table ( without using external third party dll ) ?

    Thanks

    Wednesday, March 25, 2020 11:13 AM
  • Hi Venkatzeus,

    We can use Excel Source and OLEDB Destination in ssis package.

    Please refer to the following links:

      Using SSIS packages to import MS Excel data into a database

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 26, 2020 7:03 AM