Regarding subfolder in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Regarding subfolder in SSIS

Author

Topic

NJDba
Starting Member

31 Posts

Posted - 2007-11-14 : 18:54:01

I would like to copy daily backup files from different folders to new location. The folder structure looks like this:

C:\backup database1\database1_db_20071114.bak
database2\database2_db_20071114.bak
msdb\msdb_db_20071114.bak
....

But I do not want to copy the backup file from some system database folders, say MSDB, so I have to choose some user database folders. What should I do? How can I extract the subfolder name from variable?

Any suggestion would be appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 19:13:10

YOuc an do this in Active X script, but you would have to build the file name using a string to account for the date.

I do it in DTS alot using something like this, pretty sure it works similar in SSIS (but don't have a 2005 box running yet to use it)


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
	Dim objFSO
	DIm strDestPath
    Dim strSourcePath

	Set objFSO = CreateObject("Scripting.FileSystemObject")
    strSourcePath = "C:\backup database1\database1_db_20071114.bak"
    strDEstPath = "F:\OtherBackups\database1_db_20071114.bak"
	objFSO.CopyFile strSourcePath, strDestPath

	Main = DTSTaskExecResult_Success
End Function

you can get the directory name of the backup location from msdb using this (from 2000...anyway)


select database_name as DB_Directory,cast(max(end_time) as smalldatetime) as BackupDate
from msdb.dbo.sysdbmaintplan_history
where RTRIM(LTRIM(database_name)) 'YourDBName'

which should return the path to the most recent backup date for the database. ( I have a procedure that uses this type of query to email me a full list of all DB's and their last back date. You can use this to build the date into a string for the the file name perhaps.

You can grab the full file path from the database as well, but since the only part that changes is the date in the .BAK...well, that's how I do it if needed.

Poor planning on your part does not constitute an emergency on my part.

NJDba
Starting Member

31 Posts

Posted - 2007-11-14 : 19:20:58

Thank you for your reply. But since I am doing migration project, and I really need to rewrite the SSIS package without ActiveX. Do you know how to do it in other ways?

Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 19:24:32

quote:
Originally posted by NJDba

Thank you for your reply. But since I am doing migration project, and I really need to rewrite the SSIS package without ActiveX. Do you know how to do it in other ways?

Thanks.

It is just VBScript. The visual basic syntax and logic can be used in SSIS from what i understand ...but I could be wrong.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources