Making SSIS Replace instead of Add on

I am working on a configuration database in SSIS. One of the modules in the package is giving me endless amounts of grief. The module is for some reason set up to add data onto the end of any data already stored in the table. Instead, I would like it to replace this data. I have tried an Execute SQL Task that should delete all of the rows in the table, but this isn't working. Is there a more efficient way to do this?

-Kyle

No, sounds like you're on the right track. Destinations only add new data. If you want to remove the old data first then you should use an Execute SQL Task with a DELETE or TRUNCATE statement. Are you getting an error?
|||

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

|||

EWisdahl wrote:

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

Yes! Provided you have permissions to truncate.

Making changes to a table with large amounts of data. Timeout?!

Hello,

I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.

'eligibility (dbo)' table
- Unable to create index 'PK_eligibility'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Any ideas on how to make the table change more efficient or change the timeout period. I have to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.

Thanks!

this sort of thing always happens to me when using EM. I use only code now.

If you look at the code behind the scenes that EM uses, it is creating a temp table, shoving all the data into that table, re-creating the original table and pushing all the data back. (if you tell it to save the script when you make the change, you'll see what I mean)

For a large table, I would probably create a new column, update the values in the new column with what is in the old column, then drop the old column. The only time consuming step would be the updates, however, you can space those out and update based on a range of values from one of your other columns. (i.e., update table set newcolumn = oldcolumn where datefield between '1/1/2001' and '2/1/2001')

This way it shouldn't have to create the index.

If you are trying to do this on a column that has a constraint, you will have to drop the constraint first. If you are changing the PK, and your PK is the clustered index, it's probably going to be messy any way you go.

Using script, you can't put your columns in different orders. They will always be added at the end.

sql

Making a view that shows the results of several different queries.

Hello,

I am trying to create a view that shows the following

Field1: Sum of Amounts from Table A
Field2: Count of Amounts from Table A

Field3: Sum of of Amounts from Table B
Field4: Count of Amounts from Table B
..
..
..
Field3: Sum of of Amounts from Table H
Field4: Count of Amounts from Table H
..
..
..
Things are a bit more complex but this is the gist.

I am using SQL 2000.

I know how to do this pretty easily using a stored procedure. But how
can I do it in a view? A SQL server won't meet my needs in this
situation.

I tried OpenQuery ('myserver', 'exec myprocedure') but get the message
that my server is not configured for data access. I tried the system
stored procedure to set data access to true but nothing seemed to
happen.

I also tried Select * from (

Select Statement1, select statement2

)

but got syntax error at the comma between statement1 and statement2.

Trying to use select Statement1 as ABC to does not seem to work either.

Is there a way to do what I want without making 15 views and then a
final view that shows them all together? I know I could probably do
something by creating a ton of functions, but it really seems this
should not be that hard...

I am definitely open to any easy suggestions!

Thanks,
RyanOn 21 Dec 2005 13:51:38 -0800, Ryan wrote:

>Hello,
>I am trying to create a view that shows the following
>Field1: Sum of Amounts from Table A
>Field2: Count of Amounts from Table A
>Field3: Sum of of Amounts from Table B
>Field4: Count of Amounts from Table B
>.
>.
>.
>Field3: Sum of of Amounts from Table H
>Field4: Count of Amounts from Table H
>.
>.
>.
>Things are a bit more complex but this is the gist.
>I am using SQL 2000.
>I know how to do this pretty easily using a stored procedure. But how
>can I do it in a view? A SQL server won't meet my needs in this
>situation.

Hi Ryan,

This can be done in a single query. You can of course encapsulate that
in a view, stored procedure, or whatever.

To prevent double table-scanning, here's a query that will scan each
table only once:

SELECT Field1, Field2, Field3, ..., Field16
FROM (SELECT SUM(Amount) AS Field1, COUNT(Amount) AS Field2
FROM TableA) AS A
CROSS JOIN (SELECT SUM(Amount) AS Field3, COUNT(Amount) AS Field4
FROM TableB) AS B
....
CROSS JOIN (SELECT SUM(Amount) AS Field15, COUNT(Amount) AS Field16
FROM TableH) AS H

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have tried what you suggested but it seems to snag with Syntax
errors. SQL accepts my Select ... AS A, B, C if I use it with the
Select * construct but it does not accept the CROSS JOIN STATEMENT

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2"...
)
AS A

CROSS JOIN

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2" and some other stuff...
)
AS B

Any ideas? I am not sure why the CROSS JOIN won't work actually. It
error out as soon as it hits the SELECT statement that follows CROSS
JOIN...|||On 21 Dec 2005 16:58:18 -0800, Ryan wrote:

>I have tried what you suggested but it seems to snag with Syntax
>errors. SQL accepts my Select ... AS A, B, C if I use it with the
>Select * construct but it does not accept the CROSS JOIN STATEMENT

Hi Ryan,

You didn't use the correct syntax. Check my previous replly and compare
it carefully with your query.

>Select * FROM
>(
>SELECT count(Amount) as count1, sum(Amount) as sum1
>FROM mytable1
>WHERE x="1" and y="2"...
>)
>AS A
>CROSS JOIN
Remove the line below!
>Select * FROM
>(
>SELECT count(Amount) as count1, sum(Amount) as sum1
>FROM mytable1
>WHERE x="1" and y="2" and some other stuff...
>)
>AS B
>Any ideas? I am not sure why the CROSS JOIN won't work actually. It
>error out as soon as it hits the SELECT statement that follows CROSS
>JOIN...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)