Renaming an Access table in an SSIS package

My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.

A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.

Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.

Anyone have any experience with doing things like this?

Approach 1elect into a new table then drop the old table.

Approach 2: Keep the old "standard" import database and delete from the standard table, then select into it from the new database (that is, instead of renaming the existing object, just select into the desired destination object) then delete/archive the random-named database.

In the past I would have used DAO and the tabledefs collection therein to rename the table, but that is rather old-school these days. No guarantee that it would work.

|||

Thanks, Dylan. I may give the DAO a try just for giggles, because the alternative is (for now) each client having their own copy of a relatively complex package.

Renaming a DTS package

I would like to rename a DTS package I've created, but I can't seem to find a way to do so.
Is there a table that contains all the packages with there properties somewhere? Thanks for the replyOriginally posted by WhiZa
I would like to rename a DTS package I've created, but I can't seem to find a way to do so.

Is there a table that contains all the packages with there properties somewhere? Thanks for the reply

Hello WhiZa.

Open that DTS package and save it as another DTS name. Then delete the original DTS package if you do not wish to keep it.

Best regards
Teck Boon

rename column

i need t-sql syntax for renaming a column.
the column name contains the [] characters.
alter table <tablename> rename [old field name] to NewFieldName
isn't working for me.
tia,
mcnewsxpthis works:
EXEC sp_rename 'MyTable.[Old Field]', 'NewField', 'COLUMN'|||use sp_rename..
I haven't seen the sytax that you have used.|||You might want to check this out ::
http://vadivel.blogspot.com/2004/08...g-sprename.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"mcnews" wrote:

> i need t-sql syntax for renaming a column.
> the column name contains the [] characters.
> alter table <tablename> rename [old field name] to NewFieldName
> isn't working for me.
> tia,
> mcnewsxp
>sql

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Removing duplicate data

I have a table that I need to remove duplicates from and the table includes
an identity column. The table contains 12 fields but our business rules are
that only 3 of the fields can make the record a duplicate. We would like to
keep the record with the min identity column. I have done this in the past,
but it was about 5 years ago and I did not insert the dups into another
table, I did it strictly with a query. Can anybody please help me out with
some code to take care of this or any suggestions on a better way of doing
this? So something along the lines of grouping the data by the fields that
we are interested in and then deleting the records where the identity column
is greater than the min identity column.
Thanks.This is the duplicates along with the
minimum id per group
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1
Join this to the original table
to give the ids of the duplicates
excluding the minimum id.
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
Put it all together to delete them
DELETE
FROM sometable
WHERE id IN (
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
)|||Do you need to export the removed duplicates to another table?
Try something like this:
INSERT INTO RemDup (...) SELECT ... FROM MyTab AS t1 WHERE EXISTS (SELECT
NULL FROM MyTab AS t2 WHERE t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND
t1.Col2=t2.Col2 AND t1.ID>t2.ID)
DELETE FROM MyTab AS t1 WHERE EXISTS (SELECT NULL FROM MyTab AS t2 WHERE
t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND t1.Col2=t2.Col2 AND t1.ID>t2.ID)
HTH,
Axel Dahmen
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
> I have a table that I need to remove duplicates from and the table
includes
> an identity column. The table contains 12 fields but our business rules
are
> that only 3 of the fields can make the record a duplicate. We would like
to
> keep the record with the min identity column. I have done this in the
past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
that
> we are interested in and then deleting the records where the identity
column
> is greater than the min identity column.
> Thanks.|||Here's an example. This will delete all but 1 of the dupes. You will need to
change table / col name accordingly
DELETE FROM _Holding_table
WHERE EXISTS(SELECT NULL FROM _Holding_table s1
WHERE s1.PhoneNumber= _Holding_table.PhoneNumber
and s1.PK_Holding_TableID > _Holding_table.PK_Holding_TableID)
HTH. Ryan
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
>I have a table that I need to remove duplicates from and the table includes
> an identity column. The table contains 12 fields but our business rules
> are
> that only 3 of the fields can make the record a duplicate. We would like
> to
> keep the record with the min identity column. I have done this in the
> past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
> with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
> that
> we are interested in and then deleting the records where the identity
> column
> is greater than the min identity column.
> Thanks.|||This is untested, perhaps you wrap this in a transaction first:
DELETE FROM SomeTable S
WHERE Idcolumn NOT IN
(
SELECT MIN(idcolumn)
FROM SOMETABLE S2
GROUP BY FirstColumn1,FirstColumn2,FirstColumn3
)
HTH, Jens Suessmeyer.