1

I'm trying to create an SSIS package using EzAPI 2012. The templates and tutorials that I've found works fine but I am not able to find anything on creating a connection to a OLEDB with SQL Authentication (username and password). Here's part of code that I'm using to create OLEDB connection:

// Creating a connection to the database:
EzSqlOleDbCM srcConn = Activator.CreateInstance(typeof(EzSqlOleDbCM), new object[] { package }) as EzSqlOleDbCM;           
srcConn.SetConnectionString(_CL_SourceServerName, _CL_SourceDBName);
srcConn.Name = "SourceDB";

EzSqlOleDbCM destConn = Activator.CreateInstance(typeof(EzSqlOleDbCM), new object[] { package }) as EzSqlOleDbCM;
destConn.SetConnectionString(_CL_DestServerName, _CL_DestDBName);
destConn.Name = "DestDB";

The names with "CL" in the beginning are variables. The EzSqlOleDbCM function does not have parameters for username and password.

Thanks,

General Grievance
  • 4,555
  • 31
  • 31
  • 45
AntT
  • 11
  • 1

2 Answers2

0

EzApi is great for the problems it solves. This is one of the many cases where it falls short. You can see in the source code they have hard coded the connection manager to use SSPI

/// <summary>
/// OleDb connection manager for SQL Server
/// </summary>
public class EzSqlOleDbCM : EzOleDbConnectionManager
{
    public EzSqlOleDbCM(EzPackage parent) : base(parent) { }
    public EzSqlOleDbCM(EzPackage parent, ConnectionManager c) : base(parent, c) { }
    public EzSqlOleDbCM(EzPackage parent, string name) : base(parent, name) { }
    public EzSqlOleDbCM(EzProject parentProject, string streamName) : base(parentProject, streamName) { }
    public EzSqlOleDbCM(EzProject parentProject, string streamName, string name) : base(parentProject, streamName, name) { }

    public void SetConnectionString(string server, string db)
    {
        ConnectionString = string.Format("provider=sqlncli11;integrated security=sspi;database={0};server={1};OLE DB Services=-2;Auto Translate=False;Connect Timeout=300;",
            db, server);
    }
}

What can be done?

  • Modify the source code to accommodate userids and passwords
  • Do as much as you can in EzApi and then revert to using the base SSIS object model - See the LoadFromXML portion but since this is connection manager, that will pretty much be everything
  • I don't think you can fake your way through it by adding Expressions to the connection manager itself as when it attempts to set metadata during development/object creation, the expressions won't yet be active
  • Give up on EzAPI - I know I have. I find using Biml far easier for the programmatic creation of SSIS packages. And it's supported whereas EzAPI appears to be abandoned.
billinkc
  • 59,250
  • 9
  • 102
  • 159
0

Here is the source code for the connection managers in EzAPI. Based on that, EzSqlOleDbCM (which is what you are using) extends EzOleDbConnectionManager which in turn extends EzConnectionManager

You are using SetConnectionString method on EzSqlOleDbCm; which unfortunately is hard coded to use only the Windows Auth.

So here are the options I would try:

Option 1:

EzConnectionManager exposes a property called ConnectionString and has a setter; so you can directly assign the connection string (with your user name and password) using this property (and avoid the SetConnectionString method call). For example:

srcConn.ConnectionString = @"Provider=my_oledb_provider;Data Source=myServerAddress;Initial Catalog=myDataBase; User Id=myUsername;Password=myPassword";

Option 2:

Additionally, EzOleDbConnectionManager exposes the UserName and Password properties; so you can use these properties also to specify your username and password. However if you this, then you cannot use the SetConnectionString method; cos that is hard coded to use Windows Auth, which means you will have to overwrite it again using previous option.

srcConn.UserName = "my_user_name";
srcConn.Password = "my_password";

Option 3:

If you are using the EzAPI source code directly in your project, I would add another convenience method called SetConnectionString, but one which accepts a user name and password.

public void SetSqlAuthConnectionString(string server, string db, string user, string pwd)
    {
        // Notice that we are just setting the ConnectionString property.
        ConnectionString =string.Format("Provider=my_oledb_provider;Data Source={0};Initial Catalog={1};User Id={2};Password={3}", server, db, user, pwd
    };

Then you can use as follows:

srcConn.SetSqlAuthConnectionString(myServer, myDB, userName, password);

Alternatively, at an even higher level: I would also give Biml a shot. If that serves your need well and good. Also, I wrote a library similar to EzApi called Pegasus; which you can find here. You can look at the source code and re-use it or use the code as is. Now both EzAPI and Pegasus are wrappers around the SSIS object model API. Thus, you can use the SSIS API directly; but using it directly would be a pain since you will write a lot of boiler plate code and repeat your code. You are better off writing your own wrapper against the official API; or use something like EzAPI or Pegasus.

If SSIS is your day job and you create a lot of packages according to some patterns, then I would recommend you definitely look into package automation. The development and testing time savings are huge and huge and huge. If you are unwilling to use 3rd party libraries (like EzApi or Pegasus), I would recommend you to take a look at the source code of those libraries and roll your own; or Biml.

Let me know if you need any guidance/comments on package automation.

Sam Kolli
  • 391
  • 3
  • 8