May 18, 2011 at 1:20 pm
All-
For the life of me, I cannot figure out why my code below is giving the attached error, any thoughts? Thanks in advance for any ideas.
public void Main()
{
SqlConnection TrgtDB = (SqlConnection)Dts.Connections["DynamicViewDB"].AcquireConnection(null);
TrgtDB = new SqlConnection(TrgtDB.ConnectionString);
string SourceDBName = Dts.Variables["SrcDBName"].Value.ToString();
TrgtDB.Open();
//Customer
string CustomerView = @"
CREATE VIEW [Customer]
AS
SELECT
ROW_NUMBER() OVER (ORDER BY c.cid) AS CustomerId,
c.[FirstName] AS FirstName,
c.[MiddleInit] AS MiddleName,
c.[LastName] AS LastName,
c.[Salute] AS NickName,
c.[PrimBDay] AS BirthDate,
CASE
WHEN [Title] = 'MR.' OR [Title] = 'MR'
THEN 'Male'
WHEN [Title] = 'MRS.' OR [Title] = 'MRS' OR [Title] = 'MISS.' OR [Title] = 'MISS' OR [Title] = 'MS' OR [Title] = 'MS.'
THEN 'Female'
ELSE 'Unknown'
END AS Gender,
NULL AS OrganizationId,
NULL AS Suffix,
CASE
WHEN c.[Title] LIKE 'Dr%'
THEN 'Dr'
WHEN c.[Title] LIKE 'Mrs%'
THEN 'Mrs'
WHEN c.[Title] LIKE 'Ms%'
THEN 'Ms'
WHEN c.[Title] LIKE 'Mr%'
THEN 'Mr'
WHEN c.[Title] LIKE 'Miss%'
THEN 'Miss'
END AS Title,
CASE
WHEN c.[BestCallTime] = 1
THEN NULL
WHEN c.[BestCallTime] = 2
THEN NULL
WHEN c.[BestCallTime] = 3
THEN 'Morning'
WHEN c.[BestCallTime] = 4
THEN 'Afternoon'
WHEN c.[BestCallTime] = 5
THEN 'Evening'
END AS BestContactTime,
CASE
WHEN c.[ProfileVal8] = 1
THEN 'All'
WHEN c.[ProfileVal8] = 0
THEN 'No'
WHEN c.[Profileval8] = 2
THEN 'Bulk'
END AS EmailBlock,
'English' AS [Language],
CASE
WHEN c.[NoMail] = 1
THEN 'All'
ELSE 'No'
END AS MailBlock,
NULL AS PreferredContactTime,
CASE
WHEN c.[PreferredCom] = 1
THEN 'Email'
ELSE 'Mail'
END AS PreferredContact,
CASE
WHEN c.[ProfileVal5] = 1
THEN 'Verbal Do Not Call'
WHEN c.[ProfileVal5] = 2
THEN 'Written Allowed To Call'
WHEN c.[ProfileVal5] = 0
THEN 'None'
END AS CallPermission,
CASE
WHEN [C].[CompRec] = 1
THEN 'Company'
ELSE 'Individual'
END AS [Type],
CASE
WHEN en.[SalesDone] = 0
THEN 0
WHEN en.[SalesDone] = 1
THEN 1
ELSE 1
END AS IsComplete,
CASE
--WHEN c.[Status] IN ('B', 'P', 'R', 'S', 'T', 'W')
--THEN 'Active'
--ELSE 'Inactive'
WHEN c.[Status] IN ('D','L')
THEN 'Inactive'
ELSE 'Active'
END AS [Status],
NULL AS SalesPersonId,
NULL AS BusinessId,
NULL AS InsuranceId,
NULL AS CreditApplicationId,
[C].[CID] AS PreviousCustomerId,
CAST(c.[SalesPerson] AS INT)AS PreviousSalespersonId
FROM " +SourceDBName + @".[dbo].[Customers] AS C
LEFT OUTER JOIN " +SourceDBName + @".[dbo].[Entries] AS En
ON c.cid = en.cid";
SqlCommand cmd_Customer = new SqlCommand();
cmd_Customer.Connection = TrgtDB;
cmd_Customer.CommandText = CustomerView;
cmd_Customer.CommandType = CommandType.Text;
cmd_Customer.ExecuteNonQuery();
Here is the error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Keyword not supported: 'provider'.
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
at ST_de6d9647050a49f483f12f678625cf8d.csproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
May 18, 2011 at 1:22 pm
oh btw, I left out the lines where I close my connection and pass success to Dts.TaskResult...
TrgtDB.Close();
Dts.TaskResult = (int)ScriptResults.Success;
May 18, 2011 at 1:35 pm
oh nm, guess I posted too soon. I was using the following expression to build the connection string for the ADO connection:
"Data Source="+@TmpViewDBInstance+";Initial Catalog="+@TmpViewSourceDBName+";Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-View_Staging-{000EBA5B-414B-447A-B0F6-D30043C11560}"+@TmpViewDBInstance+"."+@TmpViewSourceDBName+";Auto Translate=False;"
But this is a string for an OLEDB connection, that made SSIS very sad 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply