Deleting Existing Data before Loading New Data

I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.

I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this.
Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?

anils wrote:

I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.

I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?

In the control flow, use an ExecuteSQL Task to call TRUNCATE (or DELETE FROM) each of the tables you want to purge, before calling the Data Flow task.
|||The load doesn't sound incremental. It sounds like you are doing "drop and replace". If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't.

If you do want to drop and replace and allow rollback if the replace fails, then you need to enable transactions. First create an Execute SQL task(s) to delete your data before the Data Flow that loads the new data. Determine or create a container that will have the same scope as the transaction you want to create. If there are no other tasks in your control flow, then the package can be the container. Otherwise you can add a Sequence Container to hold the tasks that will be part of the transaction. On the container change the TransactionOption to "Required". Make sure it remains the default "Supported" for each task. Now if the Data Flow fails for some reason, the deletes will be rolled back.

|||Thanks JayH for the reply.

"If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't"

Could You please explain in detail on how to do this?
|||

anils wrote:

Thanks JayH for the reply.

"If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't"

Could You please explain in detail on how to do this?

You can use the Lookup component to find rows that don't exist in your destination table by redirecting the row on a lookup failure. See Method #2 of this article: http://www.sqlis.com/311.aspx

If you need to detect changes and not just new rows, then it becomes trickier and you may find this helpful: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Delete Textfiles after dataload

I am transferring data from text file to sql server.I have created .dtsx packages. After the package executes i need to remove the data from the text file or even remove the text files. But i want my package to run it receives new textfile.What do i need to do?Please help?

The file system task on the control flow can delete the file for you.|||Thanks it worked...|||where does the deleted file get stored...|||If the files were good, they go to textfile and binary heaven. If the files were bad (corrupt), they might go to purgatory or worse |||

sureshv wrote:

where does the deleted file get stored...

Umm, they don't. They get deleted.

Delete rows in Excel From DTS package

I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than replaces.

I can't delete the excel files on a daily basis, as they have to be
there for the DTS package to be able to export to Excel. What I want
to do is create a VBScript (ActiveX Control) to delete all the rows of
data except the first row within each spreadsheet as the first step of
the DTS package. Then the remaining steps would run and the
spreadsheets would only have the current day's data at the end of the
process.

Thanks for any help offered.I would recommend using a pull rather than push strategy with Excel.

Have your DTC package deposit the data in a report table and then embed
queries in the Excel spread sheet to grab that.
Your users could just hit the refresh button on their spreadsheets to get
the latest and greatest data (or write VBA script in the open even to
refresh it auto-magically).

You may be able to invoke the Excel App from the DTC script to get it to
call the refresh function as well.

We had the same issue with exporting to Excel. My crackpot theory was that
it was using the Excel ODBC driver, which appears not to be able to rewind
when streaming data into a spreadsheet. You may be able to delete the rows
to reset the spreadsheet in a separate operation from adding the new rows,
or do what we ended up doing - having DTC do a file-copy to overwrite a
template on-top of the target spreadsheet.

Happy Trails ...

<smonczka@.hotmail.com> wrote in message
news:1109285580.060720.12490@.z14g2000cwz.googlegro ups.com...
>I have a DTS package that needs to refresh data in 3 separate Excel
> spreadsheets on a daily basis. The problem is that unless I manually
> delete the previous day's data, it appends rather than replaces.
> I can't delete the excel files on a daily basis, as they have to be
> there for the DTS package to be able to export to Excel. What I want
> to do is create a VBScript (ActiveX Control) to delete all the rows of
> data except the first row within each spreadsheet as the first step of
> the DTS package. Then the remaining steps would run and the
> spreadsheets would only have the current day's data at the end of the
> process.
> Thanks for any help offered.

Delete row when data is numeric?

I'm using a DTS package to import a large CSV file. There is a particular column that contains text or numbers. I want to delete the row if that column has a number, I've used IsNumeric in the selection portion of the statement, but can't figure out how to use it as part of my where clause.Never mind - i got it right after I posted... it has been a long week and I'm not thinking clearly any longer: Where IsNumeric(columnName)=1

Delete records in SQL table, which are present in Excel

Hi everyone,

I have a task to create a dts package, which will delete records in a SQL table, which are present on a sheet tab in Excel. I know how to transfer records from Excel to SQL table and vice versa, but not sure how I can delete the records in SQL table, which are present in Excel. Note that there are just 2 columns - one is a key and the other name.

Thank you all in advance,
SauravI would NEVER recommend using Excel as source data for a relational database. You are begging for data corruption.

At the very least, load the Excel data into a staging table within your database where it can be cleansed and verified before you start deleting production data based upon its contents.

Bad idea. What is the source of the Excel data?