Using API, add a MSSQL datasource to a campaign and load tables

[Brett Ussher]

Hey, folks;

I'm been drudging through the documentation trying to sort this out and the closest I've gotten so far gives me a new MS SQL Server datasource in my campaign, but when I check the configured settings (CheckOut / CheckIn), the fields for the server, database, and user are populated with random segments of the connection string.  I also suspect the password field is similarly messed up, but can't read that one, of course.

Here is the code I'm using:

DataSource_SSPSoapClient datasource_ssp = new DataSource_SSPSoapClient();
JobTicket_SSPSoapClient jobticket_ssp = new JobTicket_SSPSoapClient();
Production_SSPSoapClient production_ssp = new Production_SSPSoapClient();
Campaign_SSPSoapClient campaign_ssp = new Campaign_SSPSoapClient();
 
string inDataSourceID = string.Empty; 

if (campaign_ssp.IsExist(<user>, <password>, "43"))
{
    DataSet campaignDatasources = campaign_ssp.GetDataSourcesDataSet(<user>, <password>, "43");
 
    foreach (DataTable dt in campaignDatasources.Tables)
    {
        foreach (DataRow row in dt.Rows)
        {
            if (row["dataSourceType"].ToString().Equals("MSQL")
            && row["dataSourceName"].ToString().Contains(sSQL.database))
            {
                inDataSourceID = row["dataSourceID"].ToString();
            }
        }
    }
}
 
if (string.IsNullOrEmpty(inDataSourceID))
{
    inDataSourceID = datasource_ssp.CreateNew(<user>
                                            , <password>
                                            , "43"
                                            , "MSQL"
                                            , <dataSourceName>
                                            , @"Provider = SQLOLEDB.1; Data Source = <SQL Instance>; "
                                            + @"Initial Catalog = <database>; User ID = <SQL User>; "
                                            + @"Password = <SQL Password>"
                                            , ""
                                            , ""
                                            , true
                                            , null);
}

Quick walkthough:  First major IF block is finding the datasource if it is already attached to the campaign.  That works fine, not worried about that.   Second major IF block checks to see if inDataSourceID was not found.  If not, then we need to make it.  Here is where things get spicy.  When looking at the properties for the new datasource created in the campaign, I see this:

So, my question is how am I using CreateNew() wrong when the datasource I'm connecting to the campaign is a MS SQL database?

Thank you,

--

Brett Ussher

[Brett Ussher]

I guess this means no one is connecting to MS SQL Server tables as a uProduce recipient table?

[couch]

I just used this code and it seemed to work fine:

dsn.CreateNew("<myUserName>"

                , "<myUserPassword>"

                , "2468"

                , "MSQL"

                , "NewDataSource"

                , "Provider=SQLOLEDB.1; Data Source=.\\xmpie;Initial Catalog=<myDatabaseName>;User ID=sa;Password=<mySaPassword>"

                , ""

                , ""

                , false

                , null);

Another option you have to debug the situation is to go to the WSAP root web folder, edit the web.config file and remove the comments that stop the detailed debugging. Create a new datasource using the uProduce Dashboard, edit the web.config and add the comments again to turn off the debugging. Then go to c:\xmplogs\w3wp - the log file will hold the full soap envelope so you can see what the dashboard is doing in the API calls.

[Brett Ussher]

Yeah, it will make the datasource with no errors, but if you go into the Campaign and look at the details for that newly created datasource, you'll see the Server, Database, and User values are odd and seemingly random substrings of the connection string used rather than the values you would expect.

[Brett Ussher]

Also, I did look at the logs (after turning on full logging) and the settings were basically the same.  The only change was the password is passed through Encrypt{}.

[couch]

I did check the campaign and look at the details. I also checked in the database itself. I did not get any splitting of the connection string across different fields like you seemed to report. Maybe you should work with XMPie support on this one?

[Brett Ussher]

What version of uProduce you use?  I thought about reaching out to support, but I'm hearing they will only support latest version and immediate previous version (if realeased in the last six months).  I'm on v7.5.1 so it falls outside that window.  But, I guess I'll have to have the argument I was trying to avoid.

Thanks for testing it out for yourself, Steve.  I do appreciate it.

On Feb 12, 2017 18:21, "couch" <sco...@gmail.com> wrote:

I did check the campaign and look at the details. I also checked in the database itself. I did not get any splitting of the connection string across different fields like you seemed to report. Maybe you should work with XMPie support on this one?

--
You received this message because you are subscribed to a topic in the Google Groups "XMPie Interest Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/xmpie-users/19ELZeYYSB8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to xmpie-users+unsubscribe@googlegroups.com.
To post to this group, send email to xmpie...@googlegroups.com.
Visit this group at https://groups.google.com/group/xmpie-users.
For more options, visit https://groups.google.com/d/optout.

[couch]

My server is v8.2.3. Obviously, the dashboard uses the same API, so if the dashboard works, the API should work also - assuming the method is called correctly.

[Brett Ussher]

Okay.  Thanks again, Steve.

On Sun, Feb 12, 2017 at 8:34 PM couch <sco...@gmail.com> wrote:

My server is v8.2.3. Obviously, the dashboard uses the same API, so if the dashboard works, the API should work also - assuming the method is called correctly.

--

You received this message because you are subscribed to a topic in the Google Groups "XMPie Interest Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/xmpie-users/19ELZeYYSB8/unsubscribe.

To unsubscribe from this group and all its topics, send an email to xmpie-users...@googlegroups.com.

[Brett Ussher]

Okay.  I figured this out.  This appears to be a bug in the XMPie API.  If you look at the connection string I'm using in the code snippet from my original post, you'll see that I'm using spaces around the equals sign (=).  If those spaces are removed, then the connection string works as expected.  So, if you are like me and tend to add spaces around the equals sign or (possibly) after the semi-colons, then you'll see this issue.  I requested a bug report with XMPie, we'll see if one is opened.