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 File

I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt
'
Is there a way I can do this ?. T-SQL syntax from DTS package ?
Thanks for any help.You can do it before creating it.
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200
AMB
"DXC" wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change th
e
> file name after it is created to add the current timestamp in the file nam
e
> (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.t
xt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script '
Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> You can do it before creating it.
> How can I change the filename for a text file connection?
> http://www.sqldts.com/default.aspx?200
>
> AMB
> "DXC" wrote:
>|||you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.
so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.
that's really about the easiest way to do this imo.
DXC wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change
> the file name after it is created to add the current timestamp in the file
> name (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it
> 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
new|||Thanks. I am already using something like this:
declare @.rename varchar(255)
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @.rename
to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:
declare @.rename varchar(255)
declare @.filename datetime
Set @.filename = (LEFT(GETDATE(), 12) )
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@.filename'
+ '.BAK'
exec master..xp_cmdshell @.rename
Thanks..........
"beginthreadex" wrote:

> you might be able to use xp_cmdshell and pass in the rename of the file ju
st
> like you would from dos.
> so you already know the path the the file and the name of the file. you ju
st
> need to create a varchar with the correct formatted date/time in the name
> and pass the entire rename/move command to xp_cmdshell just like from dos.
> that's really about the easiest way to do this imo.
>
> DXC wrote:
>
> --
> new
>|||'***************************************
*******************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'***************************************
*********************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMM
DDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(syst
ime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & c
str(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)|||'***************************************
*******************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'***************************************
*********************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMM
DDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(syst
ime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & c
str(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)

Rename File

I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
Is there a way I can do this ?. T-SQL syntax from DTS package ?
Thanks for any help.
You can do it before creating it.
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200
AMB
"DXC" wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change the
> file name after it is created to add the current timestamp in the file name
> (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script ?
Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> You can do it before creating it.
> How can I change the filename for a text file connection?
> http://www.sqldts.com/default.aspx?200
>
> AMB
> "DXC" wrote:
|||you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.
so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.
that's really about the easiest way to do this imo.
DXC wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change
> the file name after it is created to add the current timestamp in the file
> name (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it
> 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
new
|||Thanks. I am already using something like this:
declare @.rename varchar(255)
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @.rename
to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:
declare @.rename varchar(255)
declare @.filename datetime
Set @.filename = (LEFT(GETDATE(), 12) )
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@.filename'
+ '.BAK'
exec master..xp_cmdshell @.rename
Thanks..........
"beginthreadex" wrote:

> you might be able to use xp_cmdshell and pass in the rename of the file just
> like you would from dos.
> so you already know the path the the file and the name of the file. you just
> need to create a varchar with the correct formatted date/time in the name
> and pass the entire rename/move command to xp_cmdshell just like from dos.
> that's really about the easiest way to do this imo.
>
> DXC wrote:
>
> --
> new
>
|||'************************************************* *********************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************* ***********************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc="\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf=fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
'Dim tsa
'Const ForAppending = 8
'set tsa=saf.OpenAsTextStream(ForAppending)
'tsa.writeline cstr(systime)
'tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)
|||'************************************************* *********************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************* ***********************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc="\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf=fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
'Dim tsa
'Const ForAppending = 8
'set tsa=saf.OpenAsTextStream(ForAppending)
'tsa.writeline cstr(systime)
'tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)

Rename File

I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
Is there a way I can do this ?. T-SQL syntax from DTS package ?
Thanks for any help.You can do it before creating it.
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200
AMB
"DXC" wrote:
> I have a text file (*.txt) created from a DTS Package. I need to change the
> file name after it is created to add the current timestamp in the file name
> (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script '
Thanks.
"Alejandro Mesa" wrote:
> You can do it before creating it.
> How can I change the filename for a text file connection?
> http://www.sqldts.com/default.aspx?200
>
> AMB
> "DXC" wrote:
> > I have a text file (*.txt) created from a DTS Package. I need to change the
> > file name after it is created to add the current timestamp in the file name
> > (i.e. date and time).
> > Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
> >
> > Is there a way I can do this ?. T-SQL syntax from DTS package ?
> >
> > Thanks for any help.|||you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.
so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.
that's really about the easiest way to do this imo.
DXC wrote:
> I have a text file (*.txt) created from a DTS Package. I need to change
> the file name after it is created to add the current timestamp in the file
> name (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it
> 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
--
new|||Thanks. I am already using something like this:
declare @.rename varchar(255)
select @.rename ='ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @.rename
to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:
declare @.rename varchar(255)
declare @.filename datetime
Set @.filename = (LEFT(GETDATE(), 12) )
select @.rename ='ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@.filename'
+ '.BAK'
exec master..xp_cmdshell @.rename
Thanks..........
"beginthreadex" wrote:
> you might be able to use xp_cmdshell and pass in the rename of the file just
> like you would from dos.
> so you already know the path the the file and the name of the file. you just
> need to create a varchar with the correct formatted date/time in the name
> and pass the entire rename/move command to xp_cmdshell just like from dos.
> that's really about the easiest way to do this imo.
>
> DXC wrote:
> > I have a text file (*.txt) created from a DTS Package. I need to change
> > the file name after it is created to add the current timestamp in the file
> > name (i.e. date and time).
> > Ex: The filename is 'Myfile.txt' and I need to make it
> > 'Myfile0506051022.txt'
> >
> > Is there a way I can do this ?. T-SQL syntax from DTS package ?
> >
> > Thanks for any help.
> --
> new
>|||'**********************************************************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)|||'**********************************************************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)sql

Removing formatting from imported file

I have a file I'm pulling from another type of database into an Excel spreadsheet and then using my dtsx package to import the spreadsheet into my SQL database. The problem I'm having is that one of the fields coming out of the database to the spreadsheet has the thousands seperator in the field that I want to use as a numeric field without the ",". Right now I have a macro that I run on the spreadsheet to reset the field to straight numbers without commas before importing it, but would like to configure my Integration package to do it automatically.

Any ideas would be appreciated.

Thanks in Advance

Bring the column in as a string, then use a Derived Column transform to clean it and cast it to a number.

Code Snippet

(DT_I4) (REPLACE( [YourColumn] ,",","" ) )