1. My Script Task use an C# assembly which provide SQL Connection data type as a public property.
SSIS Global variable as Object data type accept it with no problem.
Dts.Variables("SSISConnection").Value = DTX55AppDefaults.GetSSISConParam()
- - - - - - - - - -
2. Inside my Script Task I declare local variable
Dim oSSISConnection As SqlClient.SqlConnection
and try to make assignment like this
oSSISConnection = CType(CObj(Dts.Variables("SSISConnection")), SqlClient.SqlConnection)
- - - - - - - -
3. When I execute my package it give me "DTS Script Task: Runtime Error"
"Unable to cast object of type 'Microsoft.SqlServer.DTS.Runtime.Variable' to type 'System.Data.SqlClient.SQLConnection'.
- - - - - - -
Any ideas how-to resolve this problem will be greately appreciated.
Thank you in advance,
Vitaliy
Cast data type problem.Please help!
Hot Topic
- response time when browse
- Problems with Identity Specification - deleting rows
- Huge disk usage during aggregation processing
- Cannot connect to SQL Server 2005 June CTP
- Encryption and database restore
- Are there any good examples on the use of the “Advanced Textbox Properties (Data Output Tab)“ ?
- Reporting services website
- Accessing April CTP OLAP cube from Excel 2003
- Subreport - Is it possible to 're-use' the data set from the parent report?
- Can't uninstall or reinstall nonfunctioning sql server
SQL Server
- Connect to SSIS Service on machine "localhost" failed:
- Management Studio Express CTP update now available
- Query performance
- intermittent failures using web synchronisation
- Books on line for SQL Server Express
- NS 2.0 SP1 Install Problem
- Inconsistent package failures
- How to import files via HTTP in SSIS
- Can't backup SQL Server 2005 Express DB using SQL Server Management Studio Express
- Anyone else finding this painful?
Cast data type problem.Please help!
microsci
1. Finally I figured it out.
The code should be the following:
----------------------------------------------------------------------
Option Strict Off
Imports DTXValidation
. . . . . .
Public Sub Main()
- - - - -
Dim oSSISConnection As Object
- - - - -
oSSISConnection = DTS.Variables("SSISConnection").Value()
- - - - -
DTXValidation.ValidateByPrefixes(oSSISConnection)
Dts.TaskResult = Dts.Success
--------------------------------------------------------------------
2. The reason for custom assembly to generate connection is that:
I need connections which is dynamically picked-up (by custom assembly) from machine.config file.
I did not find a way to use ConnectionManager for that because it require to set-up pre-defined connection string into SSIS environment.
Please correct me if I missed some points here.
Thank you,
Vitaliy
yanivpinhas
Changing the connection string "dynamically" has been addressed in a couple of ways.
The first are Configurations. Indeed one of the options is an Xml file. The whole idea is to allow you to store settings outside of the package and apply them immediately prior to execution, even better really than during execution.
The other is the ability of the execution tools to actually set the connection string when they load the package, and before it is executed. Have a look at the Connections page in DTExcUI for example. There is similar functionality for scheduled jobs through the SQL Agent sub-system or just using DTEXEC in a CmdExec step.
Machine.config is a nice place, but I would stick with the SSIS supported Configurations, as I see that as more than sufficient, and just less hassle to implement The choice however is yours.
CB812
Vachan
Just to be absolutely certain...
Dts.Variables("SSISConnection").Value.ToString
Ceres
This does beg the question, why use the custom assembly to generate the connection Would it not be better to use the ADO.NET connection which can return a SqlConnection from AcquireConnection, this way you stick within the SSIS paradigm of using connection managers to handle all resource pointers, in other words all connections.