Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Integration Services

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

[6691 byte] By [jkv10] at [2007-12-22]
«« Software Development with DirectX 10
»» Multiple Datasets, single data source and MDI
# 1
Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)

TimothyNgMSFT at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 2

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

jkv10 at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 3

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

DouglasL at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 4
What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.

TimothyNgMSFT at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 5

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

jkv10 at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
SQL Server
SQL Server Integration Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified