VB Script to Script task in SSIS RSS

3 replies

Last post Mar 25, 2020 11:33 AM by mgebhard

‹ Previous Thread|Next Thread ›

Shortcuts

venkatzeus

Participant

1443 Points

2030 Posts

VB Script to Script task in SSIS

Mar 24, 2020 10:48 AM|venkatzeus|LINK

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

Sean Fang

Contributor

2890 Points

847 Posts

Re: VB Script to Script task in SSIS

Mar 25, 2020 03:18 AM|Sean Fang|LINK

Hi venkatzeus,

venkatzeus
Is this data reading excel file line by line ?

Yes, it does read the data line by line. More precisely, it reads the data cell by cell.

venkatzeus
How to convert the below to script task code of SSIS ?

You could use https://converter.telerik.com/ to convert the code from VB/C# to C#/VB respectively. The result might be not very corresponding to the original code but much more readable. You can paste them into your editor and make a modification on them. (For example, you might need to change the code from Cell(0) to Cell[0] and they probably cannot identify the Object which comes from import package. )

Convert Result:

public void Read_Files()
    {
        var fso, f1, d1, file, wrk, fcount, ext;
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 75
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	Set fso = CreateObject("Scripting.FileSystemObject") 

 */
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 130
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	Set d1=fso.GetFolder(Dir) 

 */
        if (Information.Err > 0)
        {
            Interaction.MsgBox("Error (002): " + Information.Err.Description + vbNewline + vbNewline + "This application will now terminate!", Constants.vbCritical, "Error!");
            return;
        };/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 329
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	
	Set xl = CreateObject("EXCEL.APPLICATION") 

 */
        xl.Application.Visible = true;

        fcount = 0;
        foreach (var file in d1.files)
        {
            ext = fso.GetExtensionName(file.name);

            if (ext == "xls" | ext == "xlsx")
            {
                lineNo = 2;
                ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 548
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
			Set wrk = xl.Workbooks.Open(file) 

 */
                if (Information.Err > 0)
                {
                    Interaction.MsgBox("Error (003): " + Information.Err.Description, Constants.vbCritical, "Error!");
                    return;
                }

                Read_Data();
                wrk.Close();
                fcount = fcount + 1;
            }
        };/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 770
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
			
	Set wrk = Nothing 

 */
        xl.Application.Quit();
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 815
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	Set xl = Nothing 

 */
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 834
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	Set d1 = Nothing

 */
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 852
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	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.", Constants.vbInformation, "Loader - Complete");
        Information.Err.Clear();
    }

    public void Read_Data()
    {
        var sql, i, i1, i2, i3;

        i = FindHeaderRow() + 1;
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 1247
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	
	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 == "" | i2 == "") & i3 != "")
            {
                msgbox("Records skipped as (column A) or (column B) is blank");
                break;
            }
            else if (i1 == "" & i2 == "" & i3 == "")
                break;
            else
            {
                var f1, f2, f3, f4, f5, f6, f7, f8;
                if (len(Format(xl.ActiveSheet.Cells(i, 6).Value)) >= 8)
                {
                    f6 = Format(xl.ActiveSheet.Cells(i, 6).Value);
                    if (DATE_FORMAT_YYYYMMDD == SniffDateFormat(f6))
                        f6 = Strings.Mid(f6, 5, 2) + "/" + Strings.Right(f6, 2) + "/" + Strings.Left(f6, 4);
                    else
                        f6 = DateTime.Month(f6) + "/" + DateTime.Day(f6) + "/" + DateTime.Year(f6);
                    f8 = Format(xl.ActiveSheet.Cells(i, 8).Value);
                    if (DATE_FORMAT_YYYYMMDD == SniffDateFormat(f8))
                        f8 = Strings.Mid(f8, 5, 2) + "/" + Strings.Right(f8, 2) + "/" + Strings.Left(f8, 4);
                    else
                        f8 = DateTime.Month(f8) + "/" + DateTime.Day(f8) + "/" + DateTime.Year(f8);
                    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 + "'," + "'" + DateTime.Now() + "'";
                    Insert_Data(sql);
                }
                else
                    msgbox("Record skipped ");
            }
            // NextRecord:
            i = i + 1;
            lineNo = lineNo + 1;
        }
        while (true);
        adoConnect.Close();
        ;/* Cannot convert EmptyStatementSyntax, CONVERSION ERROR: Conversion for EmptyStatement not implemented, please report this issue in '' at character 3300
   at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.DefaultVisit(SyntaxNode node)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.VisitEmptyStatement(EmptyStatementSyntax node)
   at Microsoft.CodeAnalysis.VisualBasic.Syntax.EmptyStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor`1 visitor)
   at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor`1.Visit(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node)
   at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

Input: 
	Set adoConnect = Nothing	

 */
    }


    public void Format(object str)
    {
        str = Strings.Replace(str, "'", "''");
        Format = str;
    }


    public void FindHeaderRow()
    {
        var i;

        if (xl.ActiveSheet.Rows.Count > 65536)
        {
            FindHeaderRow = 1;
            return;
        }

        for (i = 1; i <= xl.ActiveSheet.Rows.Count; i++)
        {
            if (xl.ActiveSheet.Cells(i, 1).Value == "First" | xl.ActiveSheet.Cells(i, 2).Value == "Next Sequence")
            {
                FindHeaderRow = i;
                return;
            }
        }

        FindHeaderRow = -99;
    }

About read data from Excel and insert to SQL

You can use an easier way to do this -- EPPlus

More details, please refer to below link which displays how to upload an excel file, transform the data to datatable and write them to SQL server database after matching the scheme of the database.

https://www.mikesdotnetting.com/article/297/the-best-way-to-import-data-from-excel-to-sql-server-via-asp-net

Hope this can help you.

Best regards,

Sean

.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.

- Reply
venkatzeus
Participant

1443 Points

2030 Posts

Re: VB Script to Script task in SSIS

Mar 25, 2020 11:03 AM|venkatzeus|LINK

Hi Sean.

I am using the Script Task option in SSIS.

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
- Reply
mgebhard
All-Star

53041 Points

23627 Posts

Re: VB Script to Script task in SSIS

Mar 25, 2020 11:33 AM|mgebhard|LINK

SSIS has standard point and click utilities for reading Excel files. I recommend learning SSIS or reading reference documentation. You've been on these forums for a long time and should know how to find and read the docs.

Google; https://www.google.com/search?q=SSIS+impoet+Excel&rlz=1C1CHBF_enUS838US838&oq=SSIS+impoet+Excel&aqs=chrome..69i57j0l7.7196j0j7&sourceid=chrome&ie=UTF-8

First link; https://docs.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-ver15

SSMS can also Import Data.

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver15

‹ Previous Thread|Next Thread ›