MS Dynamics SL - System Database is not available in the Find Database screen (98.000.01)

This is happening after upgrading from Dynamics SL 2011 to Dynamics SL 2015.

The issue was caused by the database not being marked as 'TRUSTWORTHY' in SYS.DATABASES (Master Database).

Ran the following statement to correct the issue (replace SYSDBNAME with the name of your 'missing' database).

ALTER DATABASE SYSDBNAME SET TRUSTWORTHY ON;

I have an SSIS package with a Foreach Loop Container that reads Excel files in a specified directory. I get an error opening the file: Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Envision" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The variable that I assign the Excel file path to does get the correct value, however the package can't open the file.

I found articles describing that I need to supply the actual Excel file Connection String in the variable, not just the file path. I had some issues doing that so instead I assigned the Excel Connection String inside a script task. That solved my issue.


Dim strFile As String
strFile = Dts.Variables("User::XLSX_File").Value.ToString
Dts.Connections("Excel Connection Manager").ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFile + ";Extended Properties=""Excel 12.0;HDR=YES"";"
Dts.Variables("User::ExcelConnString").Value = Dts.Connections("Excel Connection Manager").ConnectionString.ToString

Only subsequently I found that the Excel Connection Manager has a property called ExcelFilePath. So it is possible I didn't have to add the Script Task :)

I am trying to generate an XML string based on data in two SQL tables. One contains Order Header data, the other one Line Item data.

My problem is that I can't get the Line Item info to appear properly as multiple elements within a single order:

This is the SQL statement:

Select 

    LTRIM(RTRIM(H.CustPONbr)) As "Transactions/Transaction/CustomerOrdNumber",             

    (

    Select LTRIM(RTRIM(InvtID)) As "data()" From X888_Amazon_Order_Line L1

    Where L1.CpnyID = H.CpnyID And L1.CustPONbr = H.CustPONbr

    FOR XML PATH (''), ELEMENTS

    ) As "Transactions/Transaction/LineItems/LineItem/InvtId"

From X888_Amazon_Order_Header H (nolock)

 where h.CustPONbr = '99999014'

For XML PATH ('ProcessEngineSubmission'), Root ('XML'), ELEMENTS

This is the result I get:

<XML>

    <Transactions>

      <Transaction>

        <CustomerOrdNumber>99999014</CustomerOrdNumber>

        <LineItems>

          <LineItem>

            <InvtId>TEST 1235 TEST 1234</InvtId>

          </LineItem>

        </LineItems>

      </Transaction>

    </Transactions>

</XML>

If I execute the inner select (replacing "data()" with InvtId), I get what I am trying to achieve:

<InvtId>TEST 1235</InvtId>

<InvtId>TEST 1234</InvtId>

I posted about this on Stackoverflow.com and got the solution there - http://stackoverflow.com/questions/35720120/how-to-generate-xml-using-sql-path-mode-with-line-items

"Nested selects need the ,TYPE extension to come back as XML"

Select 

    LTRIM(RTRIM(H.CustPONbr)) As "Transactions/Transaction/CustomerOrdNumber",             

    (

    Select LTRIM(RTRIM(InvtID)) As "data()" From X888_Amazon_Order_Line L1

    Where L1.CpnyID = H.CpnyID And L1.CustPONbr = H.CustPONbr

    FOR XML PATH (''), TYPE

    ) As "Transactions/Transaction/LineItems/LineItem/InvtId"

From X888_Amazon_Order_Header H (nolock)

 where h.CustPONbr = '99999014'

For XML PATH ('ProcessEngineSubmission'), Root ('XML'), ELEMENTS

SSIS OLE DB Source changes how Date values are returned

I am creating an SSIS package which is reading from a SQL Server database. The data returned needs to be imported into an ERP system.

The SQL statement I use in the OLE DB Source returns the Date values formatted this way: 01/01/1900. The dates need to be formatted this way in order for the ERP import to work. So this is working fine when the SQL statement is run in SQL Management Studio.

However when I execute the statement as part of the task in the SSIS package, the Date values are returned formatted like this: 1900-01-01. This does not work for my ERP import.

The only way I found which let me keep the format I need (01/01/1900) was to return the Date columns as Strings enclosed in double quotes ("01/01/1900").

Then I added a Derived Column task to remove the double quotes using this expression: REPLACE(Col9,"\"","")