Script task: ADODB connection and recordset

Hi,

I used adodb connection and recordset in script task. but i have an error saying adodb is not defined. how do i add it to reference? or, is adodb can run in script task or only ado.net?

cherrie

Some information in here about moving DLLs about that may help: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

-Jamie

|||

Hi,

It's mentioned on your blog that you used a variable object and assigned that to recordset destination. I did the same. I created, first, an "ADODB" variable with object data type. then I added a data flow task in my project. Also inside it, i put an Oledb source connected to my recordset destination where the adodb variable is assigned. Then I created a script task connected to the data flow task (which handles the recordset destination). however, when i began using my adodb variable in the scriptask (dim cn as adodb), it says type 'adodb' is not defined.

Did i miss something?

Cherrie

Script task User defined variable and Data Reader Source SQL Statement

Hello,

Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?

The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.

The SQL command in the custom properties doesn't cross reference back to the variable.

If anyone can help or needs further detail to help please post a resonse.

Thanks,

Marcus Simpson

The SqlCommand property can be overridden with a property expression. For data flow components expression are exposed on the parent Data Flow task rather than at the individual component level.

If you are not already familiar with expressions, look them up in Books Online.

To quickly set an expression, select the Data Flow task and hit F4. Then find the Expressions property in the property grid (as shown by the F4 key) and then select the property for the your DR source, SqlCommand. The name will be something like [DataReader Source].[SqlCommand]. Now add an expression that uses the variable as part of the command.

|||

Darren,

Thankyou very much for your response. It was correct.

Marcus.

|||

Marcus Simpson wrote:

Darren,

Thankyou very much for your response. It was correct.

Marcus.

Please mark Darren's post as answered.

Script Task Error --Object reference not set to an instance of an object

I am trying to execute this code feom Script task while excuting its giving me error that "Object reference not set to an instance of an object." The assemblies Iam referening in this code are there in GAC. Any idea abt this.

Thanks,

PublicSub Main()

Dim remoteUri AsString

Dim fireAgain AsBoolean

Dim uriVarName AsString

Dim fileVarName AsString

Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection

Dim emptyBytes(0) AsByte

Dim SessionID AsString

Dim CusAuth As CustomAuth

Try

' Determine the correct variables to read for URI and filename

uriVarName = "vsReportUri"

fileVarName = "vsReportDownloadFilename"

' create SessionID for use with HD Custom authentication

CusAuth = New CustomAuth(ASCIIEncoding.ASCII.GetBytes(Dts.Variables("in_vsBatchKey").Value.ToString()))

Dts.Variables(uriVarName).Value = Dts.Variables(uriVarName).Value.ToString() + "&" + _

"BeginDate=" + Dts.Variables("in_vsBeginDate").Value.ToString() + "&" + _

"EndDate=" + Dts.Variables("in_vsEndDate").Value.ToString()

Dim request As HttpWebRequest = CType(WebRequest.Create(Dts.Variables(uriVarName).Value.ToString()), HttpWebRequest)

'Set credentials based on the credentials found in the variables

request.Credentials = New NetworkCredential(Dts.Variables("in_vsReportUsername").Value.ToString(), _

Dts.Variables("in_vsReportPassword").Value.ToString(), _

Dts.Variables("in_vsReportDomain").Value.ToString())

'Place the custom authentication session ID in a cookie called BatchSession

request.CookieContainer.Add(New Cookie("BatchSession", CusAuth.GenerateSession("EmailAlertingSSIS"), "/", Dts.Variables("in_vsReportDomain").Value.ToString()))

' Set some reasonable limits on resources used by this request

request.MaximumAutomaticRedirections = 4

request.MaximumResponseHeadersLength = 4

' Prepare to download, write messages indicating download start

Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", _

Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), String.Empty, 0, fireAgain)

Dts.Log(String.Format("Downloading '{0}' from '{1}'", Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), 0, emptyBytes)

' Download data

Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

' Get the stream associated with the response.

Dim receiveStream As Stream = response.GetResponseStream()

' Pipes the stream to a higher level stream reader with the required encoding format.

Dim readStream AsNew StreamReader(receiveStream, Encoding.UTF8)

Dim fileStream AsNew StreamWriter(Dts.Variables(fileVarName).Value.ToString())

fileStream.Write(readStream.ReadToEnd())

fileStream.Flush()

fileStream.Close()

readStream.Close()

fileStream.Dispose()

readStream.Dispose()

'Download the file and report success

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

' post the error message we got back.

Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

EndTry

EndSub

It isn't clear what is failing, a line number or similar would help. Since you mention external assemblies, have you read this -

Referencing Other Assemblies in Scripting Solutions
(http://msdn2.microsoft.com/en-us/library/9b655bcd-19f6-43d8-9f89-1b4d299c6380.aspx)

Script task DLL reference w/o being in GAC

I need to add a reference to a DLL from a script task. But I do not have the ability to put the DLL in the GAC. The DLL is a 3rd party DLL and they do not want to strong name it because that creates a chain effect having to strong name all DLL's that are used in their projects that share the DLL I need to refernce.

Ok my question is.. Can i reference that DLL somehow in the script task by manually adding the reference at runtime? I found this next snippet of code on the internet, i cant get it to work though.

Public Overridable Function CreateReferenceItem(ByVal itemName As String, ByVal assemblyName As String) As IVsaReferenceItem
Debug.Assert(Not (itemName Is Nothing) AndAlso Not (itemName = String.Empty))
Me.AssertEngineItemUnique(itemName)
Dim item As IVsaReferenceItem = CType(_engine.Items.CreateItem(itemName, VsaItemType.Reference, VsaItemFlag.None), IVsaReferenceItem)
item.AssemblyName = assemblyName
Return item
End Function

Have you thought about using Assembly.LoadFrom()?

I think that would be a lot easier. You can use any assembly you have access to, and don't need to reference it.

|||OK? how do i do that? in a script task? dts.assembly.loadfrom()? What do i need to import in order to get that assembly.loadfrom? do you have a sample code snippet? Thanks!|||Look at System.Reflection namespace, MSDN has lots of documentation and samples:
http://msdn2.microsoft.com/en-us/library/system.reflection.assembly.loadfrom.aspx|||Here is a script task that uses the Assembly object from the System.Reflection namespace. It loads a plane assembly that has not been strong named or put in the GAC and calls a method on it passing it a single String as a parameter. I tested this code with real values on one of my assemblies first. The names have been changed to protect the innocent. ;-)
It's a lot of code however.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Reflection
Imports System.Windows.Forms

Public Class ScriptMain

Public Sub Main()
'
' Add your code here
'
Dim myAssembly As Assembly
'load the assembly
myAssembly = Assembly.LoadFrom("C:\Path\To\The\Assembly.dll")

'get a reference to the type
Dim t As Type = myAssembly.GetType("Namespace.Name.Of.Class")

' create an instance of the object
Dim obj As Object = Activator.CreateInstance(t, True)

' create an array Type objects for the parameters
' This method only accepts one parameter that is a string
Dim argtype(0) As Type
argtype(0) = "".GetType()

'get the method
Dim mi As MethodInfo = t.GetMethod("MethodName", argtype)

'similar to above. No specifying an array of the
' parameter values
Dim params(0) As Object
params(0) = "my parameter value"

'invoke the method and get the results
Dim results As Object = mi.Invoke(obj, params)

' Display the results as a string
MessageBox.Show(results.ToString())

Dts.TaskResult = Dts.Results.Success
End Sub

End Class|||Thanks that is just what I am looking for. Very helpful I will try this out. Thanks All!

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help