Posted 24 January 2025, 4:02 am EST
We have implemented a custom data set using IDataSetsService and trying to add a default data set with default data source having connect string type as external like below,
defaultDataSets.Add("DefaultDataSet", new DataSetTemplate
{
DataSource = new DataSource
{
Name = "DefaultDataSource",
ConnectionProperties = {
DataProvider = "SQL",
ConnectString = "C:\\Folder\\DataSource.txt",
ConnectStringType = ConnectStringType.External
}
},
DataSet = new DataSet
{
Name = "DefaultDataSet",
Query = {
CommandText = "SELECT ID FROM USER",
DataSourceName = "DefaultDataSource"
},
Fields = {
new Field {
Name = "Id",
DataField = "ID"
}
}
}
});
The DataSource.txt file has the exact connection string to connect to the SQL database. If we add this default data set to a report without modifying anything and try to preview or run the report the actual connection string is substituted in run time and displays the data as expected. However if we try to modify the data set, for example changing the query and try validate like we normally do before saving it, it fails to validate and throwing SQL error as “Format of the initialization string does not conform to specification starting at index 0.” and unable to save the data set due to this. It seems that it takes the file path instead of connection string content from the file for validation.
Kindly help us to fix it or suggest any alternative to perform this.