Troubleshooting SCCM Software Update Deployment Package distribution due to missing directories

I have SCCM running in my lab and ran into an issue on several occasions where the Deployment Package I created for the Software Updates started to error out on when updating the Distribution Points.  In reviewing the package distribution manager log I would see the following message:

Severity Type Site code Date / Time System Component Message ID Description
Error Milestone 002 01/09/2013 7:40:04 PM CM01.contoso.com SMS_DISTRIBUTION_MANAGER 2306 The source directory "\\contoso.com\SoftwareUpdates\c34e2458-681f-4a8b-8941-a460c2de314a" for package "0020000D" does not exist. The operating system reported error 2: The system cannot find the file specified.     Solution: Make sure you have specified a valid package source directory on the Data Source tab in the Package Properties dialog box in the Configuration Manager Console. If you specify a local path, it must be a local path on the site server. If you specify a path on a network drive, verify that the path exists and that the path is typed correctly.

I read the above message and said to myself, "Now, where'd it go?  Wait a sec... What is "it"?"

The challenge is that SCCM was tracking that the update had been download, but for some reason it wasn't in the location it was supposed to be.  When going into the console, setting the search criteria for "All Software Updates" to "Required >= 1" and "Installed >= 1" showed everything was downloaded according to SCCM.  Thus it took a little bit more digging for me to get this sorted out.  Since answers were sparse in regards to how to troubleshooting this outside of "review the logs", I figured I would share my solution.

While I still don't know WHY SCCM thought the update had been downloaded and yet the files were missing from the package source, I could at least figure out how to find the updates in the console to re-download them.

In the SQL query below just paste the highlighted (match the colors) from the above error message into the highlighted spots below.

DECLARE
@MissingSourceDirectory
NVARCHAR(512)
DECLARE
@PackageId
NVARCHAR(8)
SET
@MissingSourceDirectory =
'c34e2458-681f-4a8b-8941-a460c2de314a'
SET
@PackageId
= '0020000D'

SELECT
CASE
        WHEN
ci.BulletinID LIKE
''
OR ci.BulletinID IS
NULL
THEN
'Non Security Update'
        ELSE ci.BulletinID
        END
As
BulletinID
    , ci.ArticleID
    , loc.DisplayName
    , loc.Description
    , ci.IsExpired
    , ci.DatePosted
    , ci.DateRevised
    , ci.Severity
    , ci.RevisionNumber
    , ci.CI_ID
FROM dbo.v_UpdateCIs
AS
ci
LEFT
OUTER
JOIN dbo.v_LocalizedCIProperties_SiteLoc
AS
loc
ON
loc.CI_ID = ci.CI_ID
WHERE ci.CI_ID IN
(
    SELECT [FromCI_ID]
    FROM
[dbo].[CI_ConfigurationItemRelations]
cir
    INNER
JOIN [dbo].[CI_RelationTypes]
rt
ON
cir.RelationType = rt.RelationType
    WHERE
cir.ToCI_ID IN
    (
        SELECT
CI_ID
        FROM
[dbo].[CI_ContentPackages]
cp
        INNER
JOIN [dbo].[CI_ConfigurationItemContents]
cic
ON
cp.Content_ID = cic.Content_ID
        WHERE
cp.ContentSubFolder = @MissingSourceDirectory AND cp.PkgID = @PackageId
    )
)

Qualification (Added 1/13/2013): It is important to be aware that the table CI_ConfigurationItemRelations can have multiple levels of relationships and there are different types of relationships. The above query worked well enough for me, so I didn't investigate further. I would suggest this reference for more details the CI_ConfigurationItemRelations table: Steve Rachui - ConfigMgr 2012–Application Model–Internals–Part I

Note: As a tip, I had several items missing from my source. I noticed in this case they were all from this month (January 2013), so after the third item month, I just went and changed the query in the console to show all "Downloaded" updates with "Date Released Or Revised" within the last month and downloaded them all as a batch.

Update 1/13/2013:

I realized I had a completely unnecessary piece of information in the original query. I had reused another query I had for which I needed the original date the update was released, not the date the most current revision was released. Below is the old query in case someone still wants to know the date the update was originally released.

DECLARE
@MissingSourceDirectory
NVARCHAR(512)
DECLARE
@PackageId
NVARCHAR(8)
SET
@MissingSourceDirectory =
'c34e2458-681f-4a8b-8941-a460c2de314a'
SET
@PackageId
= '0020000D'

SELECT
CASE
        WHEN
ci.BulletinID LIKE
''
OR ci.BulletinID IS
NULL
THEN
'Non Security Update'
        ELSE ci.BulletinID
        END
As
BulletinID
    , ci.ArticleID
    , loc.DisplayName
    , loc.Description
    , ci.IsExpired
    , orig_postdate.DatePosted
as
origDatePosted
    ,
ci.DatePosted
    , ci.DateRevised
    , ci.Severity
    , ci.RevisionNumber
    , ci.CI_ID
FROM dbo.v_UpdateCIs
AS
ci
LEFT
OUTER
JOIN dbo.v_LocalizedCIProperties_SiteLoc
AS
loc
ON
loc.CI_ID = ci.CI_ID
LEFT
OUTER
JOIN
(
    SELECT BulletinId,
MIN(articles.DatePosted)
As DatePosted
FROM
    (
        SELECT
ArticleId,
MIN(DatePosted)
As DatePosted
        FROM .[dbo].[v_UpdateCIs]
        GROUP
BY
ArticleId
    )
as articles
    INNER
JOIN
[dbo].[v_UpdateCIs]
ci
ON
articles.ArticleID = ci.ArticleId
    GROUP
BY
BulletinId
)
As orig_postdate
ON
ci.BulletinId = orig_postDate.BulletinId
WHERE ci.CI_ID IN
(
    SELECT [FromCI_ID]
    FROM
[dbo].[CI_ConfigurationItemRelations]
cir
    INNER
JOIN [dbo].[CI_RelationTypes]
rt
ON
cir.RelationType = rt.RelationType
    WHERE
cir.ToCI_ID IN
    (
        SELECT
CI_ID
        FROM
[dbo].[CI_ContentPackages]
cp
        INNER
JOIN [dbo].[CI_ConfigurationItemContents]
cic
ON
cp.Content_ID = cic.Content_ID
        WHERE
cp.ContentSubFolder = @MissingSourceDirectory AND cp.PkgID = @PackageId
    )
)