1

I am new to SSIS script task, I tried the below code to run a sql query in SSIS script task but I am end up with an error.

ConnectionManager cm = Dts.Connections["oledb"]; 
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100; 
OleDbConnection sqlConn = cmParams.GetConnectionForSchema() as OleDbConnection;

ErrorMessage:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface

I am trying to find DB Server name and try to assign the value to $Project::variable using Script task.

Is there anyway I can do this.

Thanks in advance.

StackUser
  • 5,370
  • 2
  • 24
  • 44
  • 1
    "I am trying to find DB Server name and try to assign the value to $Project::variable using Script task.". You're doing this backwards. You should be defining the server name in a project or package parameter, then applying that to your connection manager. – Nick.Mc May 09 '18 at 12:39
  • Also, parameters, project and package, are read only during execution – billinkc May 09 '18 at 13:43
  • You may want to refer to this I guess [OLEDB And Ado.net within script task](https://stackoverflow.com/questions/11265706/missing-library-to-reference-oledb-connection-types) – rvphx May 09 '18 at 18:20
  • @Nick.McDermaid: Our Server has "Always on" enabled. We are connecting the DB using Listener. I can't assign the server name directly in the project parameter. If i do so then it would be static. – StackUser May 10 '18 at 08:13
  • @rvphx: I googled and tried all possible scenarios, but no luck. – StackUser May 10 '18 at 08:14
  • It’s wise to explain the full story first time. Normally you connect to the listener (which is static) then it decides where to redirect you. – Nick.Mc May 10 '18 at 09:48

0 Answers0