You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.
It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.
This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess one reason why you asked this. Again, CLR or sp_OA% will do it.
One thing to note is that almost every method and property in SMO maps to a SQL command or query. So using T-SQL to call SMO which is effectively T-SQL is circular.
And to get the stored procedure definition you'd use OBJECT_DEFINITION... to get the other properties available in SMO you'd use OBJECT_PROPERTY or query the system objects.
Although you don't mention it in the question, I assume that the reason you're using an SSIS package rather than simply executing the stored proc from a SQL Agent job is that there are other steps in the process. My other assumption is that you schedule the SSIS package using a SQL Agent job.
The first error message implies that you are creating an OLEDB connection to your Access database but that the database could not be found. It's possible that this is a security problem, and that the Windows account under which the SSIS package is running does not have permissions to the file system location where the Access database is stored.
The second error message seems to back this up - when executing the SP, the linked server connection could not be opened.
To confirm this as the issue, you need to try running the SP in SSMS using the same account under which the SQL Agent service runs.
If this is the problem, you will need either to change the permissions of the SQL Agent service account to allow access to the target location, or copy the access db to a location that the account can access (possibly the local disk) before running the package.
EDIT - in response to additional details:
Now that permission and Networked Drive are mentioned, I think this
may be the problem. However, I am still confused as to why I am able
to manually execute the Stored Procedure from within SQL Server with
the Access DB on a Network Drive?
The Acess linked server is configured to use the security credentials of the account executing the query. When you execute the procedure from SQL Server Management Studio, you are doing so in the context of your Windows account, which must have permissions to the network share.
When the same query is executed by the SQL Agent service, it uses the security context of the service account under which the service is running, which does not have access to the share.
Finally, how can I apply this suggestion? Meaning where in SQL Server
do I configure this feature:
change the permissions of the SQL Agent service account to allow access to the target location
You can find out and change the account under which the SQL Agent service is running in the SQL configuration manager (should be found under Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools. Find "SQL Server Agent" in the list of SQL Server Services and see the "Log On As" column). You may need to switch this to a domain account to get access to the share.
Best Answer
I don't think there's any way to do this with a parse, but you could try running the script inside a transaction then rolling back that transaction at the end. If there's an error, it should throw it, but even if there isn't, there won't be any lasting changes.