Hi,

I have a package that loads data from oracle to sql server . The package runs successfully in the visual studio. BUt to be able to schedule automated job, i have created the package configuration  but that's not running at failing with error below.

Error: 0xC0024108 at Package1, Connection manager "SSQLConfig": The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

I have created the xml config file and also created sqlserver configuration table to store the data connection info.

the config file has the following info. regarding connect string 

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo
 GeneratedBy="yyyy\yyyyy" GeneratedFromPackageName="Package1" 
GeneratedFromPackageID="{DFD3B752-F35B-4773-8DDA-48DA66C71877}" 
GeneratedDate="1/27/2015 10:44:45 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" 
Path="\Package.Connections[SSQLConfig].Properties[ConnectionString]" 
ValueType="String"><ConfiguredValue>Data Source=xxxxxxx;
Initial Catalog=MSTR_WH;Provider=SQLOLEDB.1;Auto Translate=False;Application
 Name=SSIS-ETL Process to pull data-{E34BA5B6-71CC-4E98-AD43-5D5CDFF0F8BA}xxxxxxx.MSTR_WH.MSTRWH;
</ConfiguredValue></Configuration></DTSConfiguration>

even the oracle connection is not working because the config file itself is not working to connect to the configuration table i believe.

Error: 0xC0202009 at Package1, Connection manager "lifenote.userid": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80004005  Description: "ORA-01005: null password given; logon denied".

Can someone help resolve this? 

Thanks

It must be this portion:

Application
 Name=SSIS-ETL Process to pull data-{E34BA5B6-71CC-4E98-AD43-5D5CDFF0F8BA}xxxxxxx.MSTR_WH.MSTRWH

Just remove the app name value completely, it is not necessary.

"\Package.Connections[SSQLConfig].Properties[ConnectionString]"

This is your problem, you need to create a variable and assign a valid value to it so that your SSIS package then picks it up while executing the package.

Below is a valid example:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="xxx" GeneratedFromPackageName="TransactionWithLookUPCode" GeneratedFromPackageID="{DB70A228-6914-434F-A374-CAB3A6229575}" GeneratedDate="15-07-2013 19:40:13"/>
</DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Variables[User::var_Dest_Path].Properties[Value]" ValueType="String">
<ConfiguredValue>C:\Users\Desktop\</ConfiguredValue></Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::var_Initial_Catalog].Properties[Value]" ValueType="String">
<ConfiguredValue>OutS</ConfiguredValue></Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::var_Source_Conn].Properties[Value]" ValueType="String">
<ConfiguredValue>Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OutS;Data Source=S900DEVGENSQL12
</ConfiguredValue>

Hi maddy_ssis,

Based on you according, the issue occurs because you didnt specify the authentication mode in the connection string of the Connection Manager.

If we clear the value for the Integrated Security property of a connection manager, it will use the SQL Server Authentication and we need to specify the SQL Server use ID and password to establish the connection. If we want to use the Windows authentication and connect to the database using the credentials of the current Windows user, the Integrated Security property of a connection manager should set to SSPI or True.

To address the issue, we need to add Integrated Security=SSPI or Integrated Security=True to the connection string to make sure the data source uses Windows integrated security.

Reference:
SqlConnection.ConnectionString Property

Thanks,
Katherine Xiong

As I understand you're using a xml file to connect to the configuration db and then trying to get configuration values set from the table.

I think issue you're facing is due to fact that you've not specified the authentication mode. Assuming its a sqlserver db you can use Windows authentication when running by yourself ie from VS shell. However if you want same to work fine when running from the job, the service account (or the account job uses) should be configured as a valid account within the db where configuration information resides. Then only it will work fine.

Second thing is there's also issue in the value you're storing inside the table for Oracle connection. As error suggests you're missing password for the connection string. So your need to add the part password=<value> to connection string stored in the table.  You may also encrypt contents of this table if you want to avoid others from seeing the stored values. In that case your package should have an additional step to decrypt them without using them to assign values to your config items.