SSIS package to create a ".done" file

[DiamondEagle]

I've created a SSIS package in the SQL Business Intelligence
Development Studio which contains a Data Flow Component. In that
component I've added some OLE DB Source items which connect to a MSSQL
2008 R2 database and run several SQL SELECT queries. I've also added a
Flat File Destination item for each of those OLE DB Source items. The
data selected in each of the OLE DB Source items is written to a text
file by the corressponding Flat File Destination item. So far so good
and the text files (.csv) are created successfully.

I now want to create a separate ".DONE" text file when each of the
.csv files is successully created. How can I do this? I specifically
only want the .DONE files to be created if the corresponding .csv file
is successfully created.

The content of each .DONE file is not important - I'll probably just
print the output of a "SELECT COUNT(id) FROM MyTable;" query in those
.DONE files.

How do I create the ".DONE" file for each of the .csv files?

Thanks.

[Bob Barrows]

Post by DiamondEagle
I've created a SSIS package in the SQL Business Intelligence
Development Studio which contains a Data Flow Component. In that
component I've added some OLE DB Source items which connect to a MSSQL
2008 R2 database and run several SQL SELECT queries. I've also added a
Flat File Destination item for each of those OLE DB Source items. The
data selected in each of the OLE DB Source items is written to a text
file by the corressponding Flat File Destination item. So far so good
and the text files (.csv) are created successfully.

Is this done in a single data flow, or is there a data flow for each text
file? I believe you will only be able to do this if you have a data flow for
each.

Post by DiamondEagle
I now want to create a separate ".DONE" text file when each of the
.csv files is successully created. How can I do this? I specifically
only want the .DONE files to be created if the corresponding .csv file
is successfully created.
The content of each .DONE file is not important - I'll probably just
print the output of a "SELECT COUNT(id) FROM MyTable;" query in those
.DONE files.
How do I create the ".DONE" file for each of the .csv files?
Thanks.

At a high level - I've never done this so I might miss a step - this is how
I would tackle this:
Add flat file connections for each of the .done files you wish to create.
Add a script task to delete the .done files when the package starts. Set the
abort-package-on-failure property for each existing data flow to false. You
want the package to keep running on failures.You might need to adjust the
max errors property of the package itself.
Add new data flows for each .done file connection, connecting each to the
data flow for the corresponding .csv flle.By default, each will only run
when the prior data flow succeeds. Edit each data flow, adding the source
and destination tasks.
If you want to raise an error, at the end you will need to add a script task
to discover missing .done files and raise an error.