3

In SSIS, I have defined a Native OLE DB\SQL Server Native Client 11.0 connection that I've calledMyConnection.

Then I've created a data flow task, and within the Data Flow of that task there is:

  1. A flat file source (from a CSV)
  2. A script component in which I'm taking the CSV output and inserting to tables via first staging in a TVP.

Within the script component I want to connect to the database using MyConnection, but I see in the Microsoft Docs:

You cannot call the AcquireConnection method of connection managers that return unmanaged objects, such as the OLE DB connection manager and the Excel connection manager, in the managed code of a Script task. However, you can read the ConnectionString property of these connection managers, and connect to the data source directly in your code by using the connection string with an OledbConnection from the System.Data.OleDb namespace.

That seems straightforward enough, so in my script task code I retrieve the connection string and try to use that to create a connection:

string conString = Connections.MyConnection.ConnectionString;
using (OleDbConnection con = new OleDbConnection(conString))
{
    try
    {
        if (con.State != ConnectionState.Open) con.Open();
        ...

And I get the error that authentication failed for the user. In the connection string there is no password (I'm using SQL Server authentication). I did check the box 'Save password' in the connection manager.

How do I use a connection defined in the project scope from within a script component?

When I provide the string explicitly, everything works. Also. Why can I not define the connection as anything but an Ole connection in the main package?

  • Take it back a step - are you certain you need to use a script task? Because I see a lot of questions on here from people using scripts when they don't need to. It's a long story but generally speaking passwords are never saved in connection strings. The entire story is very confusing but it's all about protection levels and not saving passwords in clear text. The easiest way around this is to just use windows authentication, or don't use a script task if you don't have to – Nick.McDermaid Jun 20 '17 at 5:40
  • From what I've read that is the way to parse into a TVP from SSIS... Do I need to use a TVP? Now that is a good question... – Zach Smith Jun 20 '17 at 5:50
  • Yes it is a good question. What are you trying to do and is all the data (the data populating the TVP and the stored procedure itself) in the same SQL Server? – Nick.McDermaid Jun 20 '17 at 7:17
  • For now the SQL Server instance and the data will be on the same box. The CSVs are about 100mb each, but are likely to get much bigger (from a 3rd party provider) – Zach Smith Jun 20 '17 at 7:21
  • Can you edit your question and explain where CSV's come into this. Are you reading data from CSV's and loading into a TVP and running a stored procedure? – Nick.McDermaid Jun 20 '17 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.