January 23, 2008 at 12:09 pm
I am trying to date a file in it's name everytime a DTS package runs. The package currently creates a txt file of the same name each time. Can anyone walk me through the steps I would need to take in order to modify this package to include a newly created file that adds the datetime of when it was created? Your assistance would be greatly appreciated.
Thanks,
David
January 23, 2008 at 12:41 pm
I pulled this from another thread: http://www.sqlservercentral.com/Forums/Topic342856-19-1.aspx#bm343852
All you need to do is basically store your command in a global variable. You can create a global variable by right-clicking the background DTS designer of your package. From the pop-up, select Package Properties. This will display a tabbed dialog. The second tab has the caption of Global Variables. Click on it to display them.
In the Name column, enter a meaningful name for you zipped filename and path. Give it a string type, and enter the name of it. It may be worth creating 2 - one for the filename and the other for the path, lets say ZippedFileName and ZippedFilePath.
What you want to do for this is to insert 2 tasks: 1 Execute Process Task that will perform the zip/unzip of your file and the other an ActiveX Script Task that will set the Execute Process Task’s properties.
The Execute Process Task needs to know which file it needs to zip/unzip. This should execute a command line application such as pkzip or in your case C:/Program Files/7-Zip/7z.exe" a -tzip "R:\Avectra\Queries\Mike Queries\Reports\Commerce Register\ReadyForZip\*.txt”. This will be set by the ActiveX Script Task.
The ActiveX Script Task will set the properties of the Execute Process Task. You could use a dynamic properties task, but you will need to do some work programmatically to work out the filename and the path. The following ActiveX VB Script will set the ProcessCommandLine property of the Execute Process Task:
Dim oPkg
Dim oCPTask
Set oPkg = DTSGlobalVariables.Parent
Set oCPTask = oPkg.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask
oCPTask.ProcessCommandLine = DTSGlobalVariables("ZippedFilePath").Value & "\" & DTSGlobalVariables("ZippedFileName").Value
Main = DTSTaskExecResult_Success
Then what you need to do next is to set the parameters on you need on the FTP Process.
Hope this helps,
Andez
As the poster of this code said, hope it helps. It should at least give you a starting point.
😎
January 23, 2008 at 12:49 pm
to Create a date variable you can use:
DECLARE @fn char(6)
SET @fn=REPLACE(convert(char(8),getdate(),1),'/','')
January 24, 2008 at 7:52 am
Whack the following into a SQL task at the end of your package, and you should be good
You'll need to edit both the path and the file name you want to rename.
HTH
Dave J
@dateVar Varchar(25),
@filepath Varchar(254), --if your file paths are longer than this, consider reorganising 😉
@filename Varchar(254),
@fullfilepath Varchar(512)
SET @filepath = 'C:\ ' --ending in backslash, can be a UNC Path \\myServer\myShareSET @filename = 'Imported.csv'
SET @fullfilepath = @filepath + @filename
SET @dateVar = Cast(Year(GetDate()) AS char(4))
+ RIGHT('00' + Cast(Month(GetDate()) AS varchar(2)),2)
+ RIGHT('00' + Cast(Day(GetDate()) AS varchar(2)),2)
-- Uncomment lines below if you want a time stamp
-- + '_' + Right('00' + Cast(datepart(hh, getdate()) As varchar(2)),2)
-- + Right('00' + Cast(datepart(mi, GetDate()) As varchar(2)),2)
-- + Right('00' + Cast(datepart(ss, GetDate()) As varchar(2)),2)
--This assumes the filename (not folder) contains one fullstop, though it will work with more.
--Given a filename of test.csv will return Test20050131.csv (31st January 2005)
SET @var = 'rename "' + @filepath + @filename + '" "' +
LEFT(@filename, CharIndex('.',@filename)-1) + @dateVar +
RIGHT(@filename, Len(@filename) - CharIndex('.',@filename) + 1) + '"'
/*
--If you use filenames with more than one full stop, consider the following
Set @var = 'rename "' + @filepath + @filename + '" "' +
reverse(left(reverse(@filename), CharIndex('.',reverse(@filename))-1) + '.' + Reverse(@dateVar) +
Right(reverse(@filename), Len(@filename) - CharIndex('.',reverse(@filename)) + 1)) + '"'
--Given a file name of my.test.csv will result in my.test.20050131.csv (31st January 2005)
--Will work with one full stop - test.csv becomes test.20050131.csv
*/
--print @var
DECLARE @result int
CREATE TABLE #testFile
([File EXISTS] bit NOT NULL,
[File IS a Directory] bit NOT NULL,
[Parent Directory EXISTS] bit NOT NULL)
INSERT INTO #testFile
exec master..xp_fileexist @fullfilepath
IF NOT EXISTS (SELECT 1 FROM #testFile WHERE [File Exists] = 1)
BEGIN
DROP TABLE #testFile
PRINT 'No file'
RETURN
END
DROP TABLE #testFile
EXEC @result = master..xp_cmdshell @var, NO_OUTPUT
IF (@result = 0) --override errorhandling
BEGIN
PRINT 'Success'
RETURN
END
ELSE
BEGIN
PRINT 'Failed'
RETURN
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply