How to make SSIS package to execute the different file name with the same structure.

Question

How to make SSIS package to execute the different file name with the same structure.

phina_el

SSC-Addicted

Points: 408
More actions
October 6, 2009 at 11:35 pm

#142397

I have TXT file as source for SSIS package every month the filename is YYMM[FileName].txt.
ex0907MID.txt, 0908MID.txt, 0909MID.txt, etc.
I need to run the same SSIS package to execute the different file name with the same structure.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
Regards,
Fina_EL

Viewing 11 posts - 1 through 10 (of 10 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

phina_el (10/6/2009)
I have TXT file as source for SSIS package every month the filename is YYMM[FileName].txt.
ex0907MID.txt, 0908MID.txt, 0909MID.txt, etc.
I need to run the same SSIS package to execute the different file name with the same structure.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
Regards,
Fina_EL

You can setup expression for a variable. The following expression will do the job (if you run the package once a month):

RIGHT((DT_WSTR, 4)YEAR(GETDATE()), 2) +

(MONTH(GETDATE()) > 9 ? (DT_WSTR, 4)MONTH(GETDATE()) : "0" + (DT_WSTR, 4)MONTH(GETDATE()) ) +

"MID.TXT"

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

phina_el SSC-Addicted Points: 408 More actions · Answer 2

Thank you CozyRoc. Really this is a Great help.

how about this case:

the results of last month's data, appeared at the beginning of the month .

eg this month: this month is October, system output file is 0909mid.txt

i want to process file 0909mid.txt

TQ..

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

phina_el (10/8/2009)
Thank you CozyRoc. Really this is a Great help.
how about this case:
the results of last month's data, appeared at the beginning of the month .
eg this month: this month is October, system output file is 0909mid.txt
i want to process file 0909mid.txt
TQ..

In this case your processing should not depend on the current day. You have to setup your package to process all files from specific folder. To do this, setup a foreach loop with file selection set to *mid.txt . Then pass each foreach loop iteration to your data flow task for processing.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

phina_el SSC-Addicted Points: 408 More actions · Answer 4

I think if the case each month, the system add data in one folder with type process is replace file,your idea can be applied.

The problem is every month the system output is added (not use replacement file)in default folder ie: MID Folder. In MID folder containing the MID file from every month, there is 0907mid.txt, 0908mid.txt, 0909mid.txt.

I think if using *MID.txt, the system will confusion because there are many files likes *MID.txt

So in this case, this month is October, I had to process the data [Current Month-1] that is 0909mid.txt

TQ

Tim Mitchell SSCoach Points: 15681 More actions · Answer 5

Are you able to make modifications to the source files? If so, consider archiving the source files to a different directory after processing using the File System Task. This will help to keep your source directory clean, and allow you to simply process all of the files in the source directory at runtime with the For Each loop.

If you can't modify the source files, using CozyRoc's suggestion about creating your date string as a filename using an expression is your best bet.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

phina_el SSC-Addicted Points: 408 More actions · Answer 6

Yes, I can modify the file, I also have to apply to the File system task with a Operation "COPY"

to another folder (i.g Folder_Temp)

I'm still confused, how to create a variable to display data on the [Current Month-1], if now October so I want copy files 0909MID.txt to Folder_Temp.

CozyRock San yesterday has helped me to display the current month in data.

phina_el SSC-Addicted Points: 408 More actions · Answer 7

Yes, I can modify the file, I also have to apply to the File system task with a Operation "COPY"

to another folder (i.e Folder_Temp)

I'm still confused, how to create a variable to display data on the [Current Month-1], if now October so I want copy files 0909MID.txt to Folder_Temp.

CozyRock San yesterday has helped me to display the current month in data.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

You can also very easily control something like this from the DTExec command line (or SQL Agent Job Step definition), either through the command line options, or through package configurations and the use of DOS Environment Variable settings.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

phina_el (10/8/2009)
Yes, I can modify the file, I also have to apply to the File system task with a Operation "COPY"
to another folder (i.g Folder_Temp)
I'm still confused, how to create a variable to display data on the [Current Month-1], if now October so I want copy files 0909MID.txt to Folder_Temp.
CozyRock San yesterday has helped me to display the current month in data.

Here is the expression for current month - 1:

RIGHT((DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())), 2) +

(MONTH(DATEADD("mm", -1, GETDATE())) < 10 ? "0" : "") + (DT_WSTR, 2)MONTH(DATEADD("mm", -1, GETDATE())) +

"MID.TXT"

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

phina_el SSC-Addicted Points: 408 More actions · Answer 10

Thanks RBarryYoung San,

but I want to try to use the full features of sql server in processing this data.

for : CozyRoc San

Thank you. Really this is a Great help.