How to add CustomerID and LastName to the file name in SSIS?

Question

Post reply

How to add CustomerID and LastName to the file name in SSIS?

sxzhu

SSC Enthusiast

Points: 178
More actions
June 25, 2023 at 3:49 am

#4218751

There are several text files that are saved in a local driver. The file names are:
Each file has only one record. The record includes fields of CustomerID, FirstName, LastName, and SubmittedDate, see below.
I would like to add CustomerID, LastName and CurrentDate to the file name. The format for file name is CustomID_LastName_FileName_CurrentDate.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

What technique are you using to create the files? A data flow within a loop, perhaps?

What method are you currently using to name the files? Script task? Dynamic variable? Something else?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sxzhu SSC Enthusiast Points: 178 More actions · Answer 2

I use a Data Flow Task within For Loop Container to split rows into files and save to a local drive. The file names are from a variable 'Counter' that is used in Flat File Connection Manager expression.

Thank you!

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

This is an interesting one. This is what I would try, I think:

First, change the way that the file-naming works. I'd use a static file name (somefile.txt, or whatever), and add a task to rename the file after the dataflow task completes, but inside the For Loop container.

The rename would be from somefile.txt to a variable User::filename.

User::filename would be set inside the dataflow task. Add a Script Component transformation between source and target and add User::filename to the read/write variables collection. Add CustomerId, LastName to the Input Columns collection.

A simple bit of C# will allow you to set the value of User::filename as required.

If you have full control over the filename format, I'd suggest you consider using YYYYMMDD_CustId_Surname.txt instead, where YYYYMMDD is 'today'. If you do this and ever find you have files in a folder created over a number of days, you'll easily be able to sort the filenames by creation date in File Explorer.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sxzhu SSC Enthusiast Points: 178 More actions · Answer 4

Many thanks! How to use a static file name because the variable Counter is dynamic?

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

sxzhu wrote:

Many thanks! How to use a static file name because the variable Counter is dynamic?

Edit your package in VS and change the definition of the flat file destination so that it no longer uses Counter.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel