Solved

Urgent and Assigned Points

Posted on 2008-10-22
34
764 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:M_Boy76
  • 13
  • 11
  • 10
34 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22778747
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
 

Author Comment

by:M_Boy76
ID: 22778805
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22778818
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
 

Author Comment

by:M_Boy76
ID: 22778936
its going to be csv file in a shared location
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22778955
ok... do you want/need an example?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22780523
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22780675
HoggZilla,
the user need to create a file each time execute a SSIS package..
I think you made some confusion!
regards!!
Pedro
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22780811
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22781203
"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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22781369
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22781563
HI!
Yes is better the author explain us better in spite of losing time!
Regards!
Pedro
0
 

Author Comment

by:M_Boy76
ID: 22783930
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
 

Author Comment

by:M_Boy76
ID: 22784091
so basically somthing like this :

@[User::FileName] + "incremental numeric number ".csv"
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22784478
ok... choose if you prefer my solution or the solution of HoggZilla!
regards
0
 

Author Comment

by:M_Boy76
ID: 22785026
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22785048
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
 

Author Comment

by:M_Boy76
ID: 22785245
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22785280
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
 

Author Comment

by:M_Boy76
ID: 22785559
I wish i could take it from there but i cant, if you could break it down further
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22785706
"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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22785884
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
 

Author Comment

by:M_Boy76
ID: 22787112
Please do PedroCGD
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22787145
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22787314
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22787337
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
 

Author Comment

by:M_Boy76
ID: 22787803
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22788340
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22788547
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22788623
In the Foreach Loop Editor, be sure to select the Name and extension radio button under the Retrieve file name group.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22788646
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22792088
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
 

Author Comment

by:M_Boy76
ID: 22794591
I got lost in trying to understand and how, when you began making changes by adding a script task
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22795356
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22795446
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now