Importing files with different extensions

Question

Importing files with different extensions

sql_Reporter

SSCrazy

Points: 2329
More actions
April 28, 2008 at 2:37 am

#125068

Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number
xbouns.A17
xbouns.B17
xbouns.C17
Some help would be great to get this working

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

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

pritesh2205 Ten Centuries Points: 1355 More actions · Answer 1

You can use for each loop for this. You can put a filter such as xbuns.??? . Use this in the File text box in the collection tab of the for each loop container.

sql_Reporter SSCrazy Points: 2329 More actions · Answer 2

Hey i need to be able to know which extension as not all of the files go into the same table.

i've the 6 files and they are named like this

xbouns.A18

xbouns.B18

xbouns.C18

xbouns.E18

xbouns.F18

xbouns.G18

The 18 part of the file keeps changing to the current week number so next week it will be example xbouns.A19

I tried to write an expression to see if i could figure out the week number but it won't work

Dave23 Hall of Fame Points: 3903 More actions · Answer 3

You could use a batch file to set standard naming prior to the actual call to SSIS:

import.bat:

rename xbouns.A%1 xbouns.bar

rename xbouns.B%1 xbouns.baz

rename xbouns.C%1 xbouns.yah

etc...

dtexec /f "C:\SSIS_Projects\loadfiles.dtsx

c:\>import 18

It's not terribly elegant, but it will accomplish what you need to do.

Chinna SSCertifiable Points: 5408 More actions · Answer 4

You can use for each loop for this. You can put a filter such as xbuns.??? . Use this in the File text box in the collection tab of the for each loop container.

I think u can go with pritesh keep a filter xbuns.??? and import in using for each loop conainer and if u want to know from which file the rows are just add sourcefile column to your table by which u can know from whichfile the row has been imported i have already tried that .....need anymore details let me know...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman