Creating a semaphore file on a network drive

Good Morning,

I'm hoping that someone can help me. I have a SQL 2005 SSIS package that will run Friday mornings to empty/load a table with data from another database. On Friday evenings I'll need to run another package, but want to make sure the table load completed prior to launch. For this I planned to use a file watcher task, however I cannot for the life of me figure out how to output a 'done' semaphore, from the morning job, to a networked drive.

A file system task will not work because there is not a 'create file' option. I do not have an existing file that I can rename either.

I tried an execute process task running cmd.exe with the following argument:

Code Snippet

echo Done> \\NetworkedServer\ftproot\Load.Done

This fails because UNC paths are not recognized. (The package executes from another server so I cannot use a local path, nor am I allowed to set-up a local share.)

Can someone offer an alternative suggestion? I'm really hoping this is easier than I'm making it.

Thank you in advance,

Roger

Why not have the first package write a value to a SQL table that the second package queries?|||You coud try a simialr aproach using a table. You can update or insert a row to indicate the status of the process. Then the next package will query that table and decide whether to run or not.

Createing a trace to load a table

Hi All,
Is there a programmatic way to create a trace that loads a table?
I created a trace that loads a table; I started it and stopped it. I
scripted the trace to SQL 2000, and tried to start the trace with the SQL
code created by the profiler in query Analyzer. I have been running these
where they load files with no problems.
I added the table name to procedure where it tells me to add a file name but
the table does not load.
Is this possible? Running to file first leaves me open for SOX to say this
is not fully a live audit, there will be up to an hour depending how fast the
files load.
TIA,
Joe
jaylou wrote:
> Hi All,
> Is there a programmatic way to create a trace that loads a table?
> I created a trace that loads a table; I started it and stopped it. I
> scripted the trace to SQL 2000, and tried to start the trace with the
> SQL code created by the profiler in query Analyzer. I have been
> running these where they load files with no problems.
> I added the table name to procedure where it tells me to add a file
> name but the table does not load.
> Is this possible? Running to file first leaves me open for SOX to
> say this is not fully a live audit, there will be up to an hour
> depending how fast the files load.
> TIA,
> Joe
There is no way to trace to a table directly with a guarantee that all
events will be captured. You need to do what you are already doing. That
is, trace to a flat file on the server itself and then migrate that
trace file information to a trace table if needed once the trace
completes. The file loads real time. It's just that you can't query the
file while SQL Server has it open. Once the trace completes or the trace
does a rollover to a second file, you can query the first.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||thanks again David.
"David Gugick" wrote:

> jaylou wrote:
> There is no way to trace to a table directly with a guarantee that all
> events will be captured. You need to do what you are already doing. That
> is, trace to a flat file on the server itself and then migrate that
> trace file information to a trace table if needed once the trace
> completes. The file loads real time. It's just that you can't query the
> file while SQL Server has it open. Once the trace completes or the trace
> does a rollover to a second file, you can query the first.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>