Connection string in BIML

Question

I'm new to BIML, and are trying to connect to a SQL Server (2017) using biml (And visual studio 2017) using a username and password. Hoverer every time I try to connect the connection goes offline, and does not work. Not even if I set to online afterwards.

I've tried to connect using SSID, and everything works just fine the problem only occurs when it comes to providing "User Id" and "Password"

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

<Connections>
        <Connection  Name="test_name" ConnectionString="Data Source=abc15\un1;Initial Catalog=extract;Persist Security Info=True;Provider=SQLNCLI11.1;User ID=biml_user;Password=biml_password;" CreateInProject="true"/>
</Connections>*

  <Packages>
    <Package Name="test_name" ConstraintMode="Parallel" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <ExecuteSQL Name="Test Select" ConnectionName="test_name">
          <DirectInput>Select 1 As One</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Error message: Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'biml_user'.".

Do you need any other information?

/Thomas

iamdave iamdave · Accepted Answer · 2018-08-21T13:51:13

You have ProtectionLevel="DontSaveSensitive" set in your Biml, which will output an SSIS package that will not - as the name suggests - save sensitive information such as usernames and passwords.

You need to either change your Protection Level or persist your credentials in SSIS Parameters.

Connection string in BIML

1 Answers