Urgent and Assigned Points

Hi there

does anyone know how i can configure a SSIS package to create an incremental file name each time it is dumped into the required locaton?

eg every time it is created it will create the filename as:

file00001, file00002, file00003
M_Boy76Asked:
Who is Participating?
 
HoggZillaConnect With a Mentor Commented:
I have attached the SSIS Package containing the solution. It should everything you need to accomplish your task. Good luck!
NOTE: I put an extension of .txt on the file. Just rename the file and remove the .txt.
HoggZilla

Package2.dtsx.txt
0
 
PedroCGDCommented:
use a script task
explain better how you need to count files...

for example you you have 2 files (file0001, file0002) in shareXPTO, next file will be file0003
and
for example you you have 2 files (file0001, file0004) in shareXPTO, next file will be file0003 or file0005
the name depends on last filename or depends in the number of files?!
regards!
0
 
M_Boy76Author Commented:
No, when the package is excuted for the 1st time it will create the csv file as file0001, when it excusted the 2nd time a week later, it create another csv file titled file0002, when excusted again on the 3rd week, it will be file0003 and the incremental each time a csv file is created?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PedroCGDCommented:
ok... you need to store the increment variable in some place... where you prefer? Database, textfile...?
Select and I do an example to you, ok?
regards!
0
 
M_Boy76Author Commented:
its going to be csv file in a shared location
0
 
PedroCGDCommented:
ok... do you want/need an example?
0
 
HoggZillaCommented:
Since you are in SSIS, I would use a database table. Set the key field to contain an ID with IDENTITY.
Use and Execute SQL Task to Insert a row in the ID table. Then select the newly inserted row which returns your unique number. Pad the number with zeros and prefix it with the word file. You can do this in the Select.
SELECT FILEID FROM MyFileIDTable or,
SELECT 'file' + dbo.vLPAD(CAST(FILEID AS VARCHAR(10), 5,'0') FROM MyFileIDTable
I attached a simple function I wrote to pad a string. (vLPAD)

CREATE  function [dbo].[vLPAD] 
(	
	@vString VARCHAR(1000),		-- String value to pad with spaces
	@vLength INT,				-- Total length of return
	@vPadString VARCHAR(1000)	-- Character String to LPAD
 
)
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @PaddedString VARCHAR(1000);
	DECLARE @CurLen INT;
	DECLARE @SpaceLen INT;
 
	SET @CurLen = LEN(@vString);
	SET @SpaceLen = @vLength - @CurLen
 
	IF @SpaceLen < 1 RETURN @vString;
 
	SELECT @PaddedString = REPLICATE(@vPadString,@SpaceLen) + @vString;
 
	RETURN @PaddedString;
END
 
 
--sample
--pad 0 to a number to get the length of 10 chars
--SELECT dbo.vLPAD('8999',10,'0')

Open in new window

0
 
PedroCGDCommented:
HoggZilla,
the user need to create a file each time execute a SSIS package..
I think you made some confusion!
regards!!
Pedro
0
 
HoggZillaCommented:
Confusion is my middle name . :-)
When the package is executed, insert a row into an "ExecutionTable". Each insert will automatically create an IDENTITY column value incremented as he chooses. Now, select the inserted row and you have your next number in the count. IE the counter.
I apologize if I miss something, how is this not what the user is asking for? Each time the package is executed I want to create a file that is related to the number of times this process has been executed? We are not talking about creating the file here, we are talking about the incremental file name, right?
0
 
PedroCGDCommented:
"eg every time it is created it will create the filename as:
file00001, file00002, file00003 "

I supose user need to create file inside SSIS.
Regards
0
 
HoggZillaCommented:
Wow, we interpreted that completely different.
M_Boy76: No, when the package is excuted for the 1st time it will create the csv file as file0001, when it excusted the 2nd time a week later, it create another csv file titled file0002, when excusted again on the 3rd week, it will be file0003 and the incremental each time a csv file is created?
Scenario:
The SSIS package is executed via SQL Server Agent, Stored Procedure or some Scheduler. The package does it's job, processes data, etc...
At some point the package will create a .csv file and put it into a directory. Each time the file is put into that directory the file should be named with a convention incremented by one, or whatever.
Two ways to do this.
1) Either store the last filename value, like I suggest in the solutuion above, using a database table, registry entry, text file or whatever - or,
2) read the files from the directory and grab the last filename by max date, parse it to get the next number, increment it and you have your next filename.
0
 
PedroCGDCommented:
HI!
Yes is better the author explain us better in spite of losing time!
Regards!
Pedro
0
 
M_Boy76Author Commented:
This is what i am looking for:

2) read the files from the directory and grab the last filename by max date, parse it to get the next number, increment it and you have your next filename.

This will satisfy my requirement of incremental file names once created in the shared directory.
0
 
M_Boy76Author Commented:
so basically somthing like this :

@[User::FileName] + "incremental numeric number ".csv"
0
 
PedroCGDCommented:
ok... choose if you prefer my solution or the solution of HoggZilla!
regards
0
 
M_Boy76Author Commented:
As you can see this expression within a variable of SSIS:

@[User::filename] +
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".csv"

this expression creates multiple csv files when they are created and dumped in the shared location.

YUP20070107, YUP20070108, YUPP20070109.

What is to create the same filesnames but with an incremental figure - YUP0001, YUP0002, YUP00003.
0
 
HoggZillaCommented:
When you do go to the "shared" directory to get the files, consider this:
1. Can users add or modify files in this directory, that could affect your file dates and names.?
2. What is the naming convention, "file" + 5 digit zero padded number + ".csv"?
If user intervention is not a problem and you know your naming convention, here are some instructions for reading filenames.
http://www.sqlis.com/55.aspx
 
0
 
M_Boy76Author Commented:
I do not want to be reading file at the shared location, but simply create files in a csv format with results sets extracted from the database
0
 
HoggZillaCommented:
Read in the most recent filename from the Shared directory into a user variable.
@[User::LastFileName]
As an example I have "YUP0007.csv"
SUBSTRING(@[User::LastFileName] , 4, 4 ) will return "0007"
 This will convert it to a number. I think you can handle it from there:
(DT_I4) ((DT_WSTR, 4) (SUBSTRING(@[User::LastFileName] , 4, 4 ))) will return 7
Good luck!!!
0
 
M_Boy76Author Commented:
I wish i could take it from there but i cant, if you could break it down further
0
 
HoggZillaCommented:
"YUP"
+  REPLICATE( "0",  4 - LEN( (DT_WSTR, 4) ((DT_I4) ((DT_WSTR, 4) (SUBSTRING(@[User::LastFileName] , 4, 4 ))) + 1) ) )
+ (DT_WSTR, 4) ((DT_I4) ((DT_WSTR, 4) (SUBSTRING(@[User::LastFileName] , 4, 4 ))) + 1)
+ ".csv"
0
 
PedroCGDCommented:
I made the example in SSIS to create File0001, File0002, based on an integer stored in CSV file.
If you want it, I complete it 100% and attach here!
Regards!
0
 
M_Boy76Author Commented:
Please do PedroCGD
0
 
PedroCGDCommented:
I made the project yesterday, because I dont right now here, I will send you in 2 or 3 hours when I arrive home!
Regards!
0
 
HoggZillaCommented:
What are you looking for? This solution is broken down to the lowest detail. Are you asking someone to build this solution for you and post the dtsx package?
0
 
PedroCGDCommented:
I created dtsx with no problem for the user. In few hours I will attach here, because I dont have with me the files created yesterday.
Regards.
0
 
M_Boy76Author Commented:
I am not asking anyone to create the package - NO, but a simple explanation on how to create a package that will create csv files, based on numerical increments.

If he has done this based on File0001, File0002, then i will like to see it, but i am most definately sure that the varible expression has got to be like:

"fileprefix" + 5 digit zero padded number + ".csv"

This solution :

"YUP"
+  REPLICATE( "0",  4 - LEN( (DT_WSTR, 4) ((DT_I4) ((DT_WSTR, 4) (SUBSTRING(@[User::LastFileName] , 4, 4 ))) + 1) ) )
+ (DT_WSTR, 4) ((DT_I4) ((DT_WSTR, 4) (SUBSTRING(@[User::LastFileName] , 4, 4 ))) + 1)
+ ".csv"

didnt work.
0
 
HoggZillaCommented:
OK, I am not tryihng to be snotty, sorry if it came off like that. I want to help but I want you to be a better developer at the end. Pedro creating the package for you should not be your goal. Looking at his work, sure, that has value.
So let's take it step by step. This will build the foundation for your incremented file name.
The expression I created above does work, but you have to understand the parts. Of course you can change these variables, but I will assume the variable names.
Step 1: You need to create a variable named: FilePrefix, the Scope should be the PackageName, the Value is your input. I will use YUP for my example. You can change it to File or file or whatever you like. Also create a variable named FileSuffix, the value will be .csv. Picture below.
Step 2: You need to create a variable named SharedDirectory. This will contain your fully qualified path to the shared directory: My example is \\server\share\directory\
Step 3. Create a variable named LastFileName, String. All variables should be scoped for the entire package so make sure you do not have a task or component selected when you create the variable.
Step 4. Create a variable named LastFileNumber, Int32. Set Evaluate as expression = True. While you have the variable highlighted you can access the properties in the "Properties" window.
Step 5: Create a variable named NewFileName. Set EvaluateAsExpression = True.
Step 6: Create a variable named IncrementLength, Int32. Put the value to how long you want your incremented number to be. I used 5.
Next, I will walk through the steps to find the last file in the shared directory.

ssis-variables.bmp
ssis-var-properties.bmp
0
 
HoggZillaCommented:
Let's get the most recent file in the shared directory:
Drag a Foreach Loop Container onto your Control Flow. Double Click to open the Foreach Loop Editor. see below. Set the Enumerator to Foreach File Enumerator.
Click on Expressions. Set the Directory to your variable using the expression. Set the FileSpec to the expression: @[User::FilePrefix] + "*" +  @[User::FileSuffix]
see the attached pics.

foreach-loop-editor.bmp
Expressions.bmp
filespec-expression.bmp
0
 
HoggZillaCommented:
In the Foreach Loop Editor, be sure to select the Name and extension radio button under the Retrieve file name group.
0
 
HoggZillaCommented:
Set the variable mapping as shown below. The LastFileName will get populated with the FileNames from the directory in a Loop.
variable-mapping.bmp
0
 
HoggZillaCommented:
OK, I finished it up and made a couple of changes.
Change the variable LastFileNumber to NewFileNumber. Add a variable called LoopFileName and set the Foreach Loop Container variable assignment to LoopFileName from LastFileName.
Here is the expression for NewFileNumber. Note, this wont work if the LastFileName variable is empty.
 (DT_I4) SUBSTRING(@[User::LastFileName] , 4 , @[User::IncrementLength]  ) + 1
Put a Script Task inside the Foreach Loop Container. See picture below which also shows the variables for Read and Write. I have also attached the code for the Script Task. Notice how we loop through the files, get the file creation date and compare it against the previous files in the loop. We save the most recent dated filename to the LastFileName variable.
Then the NewFileName variable is calculated as an expression using the following code:
@[User::FilePrefix]
+  REPLICATE( "0",  @[User::IncrementLength]  - LEN( (DT_WSTR, 5) @[User::NewFileNumber]  )  )
+  (DT_WSTR, 5) @[User::NewFileNumber]
+  @[User::FileSuffix]
 Place a Script Task as a successor to the Foreach Loop Container. The code will show you a MsgBox with the New File Name and Last File Name. In my example here I have 5 files in the directory, the last was YUP00005.csv.
Dim LastFileName As String = CStr(Dts.Variables("LastFileName").Value)
Dim NewFileName As String = CStr(Dts.Variables("NewFileName").Value)
MsgBox("Last File: " + LastFileName + vbCrLf + "New File Name: " + NewFileName)

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO.File
Imports System.IO.FileInfo
 
 
Public Class ScriptMain
 
	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.
 
	Public Sub Main()
		'
        ' Add your code here
 
        Dim LoopFileName As String = CStr(Dts.Variables("LoopFileName").Value)
        Dim Path As String = CStr(Dts.Variables("FileShareDirectory").Value)
        Dim LoopFilePath As String = Path + LoopFileName
        Dim File As System.IO.File
 
        Dim CheckFileDate As DateTime = File.GetCreationTime(LoopFilePath)
        Dim FileDate As DateTime = CDate(Dts.Variables("FileDate").Value)
 
        If CheckFileDate > FileDate Then
            'This means the file we are looking at now is the newest so far
            Dts.Variables("FileDate").Value = FileDate
            Dts.Variables("LastFileName").Value = LoopFileName
        Else
            'This means the file we are looking at is older, ignore it
        End If
        'MsgBox(FileDate.ToString)
        'MsgBox(Dts.Variables("LastFileName").Value.ToString)
 
		'
		Dts.TaskResult = Dts.Results.Success
	End Sub
 
End Class

Open in new window

scripttask.bmp
filename.bmp
0
 
M_Boy76Author Commented:
I got lost in trying to understand and how, when you began making changes by adding a script task
0
 
PedroCGDCommented:
Dear M_Boy76,
Follow the package of the HoggZilla. It works for what you need. You only need to create the file, but if working good! I dont send you my project because HoggZilla has better performance.
Regards!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.