How to load the filename and the data from multiple text file using SSIS package at the same time?

Question

How to load the filename and the data from multiple text file using SSIS package at the same time?

deepakagarwalathome

SSC Enthusiast

Points: 166
More actions
December 4, 2011 at 2:12 pm

#263939

Hi Everybody
This is my first posting on this forum - so please bear with me if I get things wrong! I will learn eventually!
I am trying to build my first SSIS package. Whilst I do know SQL and also VB, I have never worked with SSIS but I need to work with it now! This is what I need to do :-
1. Load a whole stack of text files into an SQL Server 2008 R2 database table - these come in two (2) formats. Which file has data in which format is dictated by the vendor names in the filenames - characters 10 through to 15 contain values like say("Test1", "Test2", "Test3") - which are the vendor names.
2. For each record inserted in to the table, the filename where this record has come from also needs to be inserted. The table has a field called "SourceFilename"
Whilst I am able to create a very basic package (one for each file format) to load the data in to a table (and I do capture the filename in a variable), I am not able to load the filename each row for the data inserted for these files. Can someone help me with this?
Secondly, I did try to create a 'ScriptTask' using VB as the language to do different processing depending upon 'Testx' values in the filenames using the "InStr()" function but it just didn't work. Any help on this would be highly valued too.
I am attaching herewith with what I have developed so far - if that helps at all? The 'Temp' folder contains the sample text files in 2 different formats - the one containing the word 'Arati' in the filename has a different format to the ones containing the word 'Deepak' in the filenames.
Thanks in advance.
Best regards
Deepak Agarwal

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

deepakagarwalathome (12/4/2011)
I have never worked with SSIS but I need to work with it now!

If expert knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mino-bike through a match box. 😛

With that thought in mind and depending on the format of the files, this is all fairly easy to do in T-SQL. Are you sure you need to do it in SSIS? And, no... you certainly don't need a VB script for any of this. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Luckily, this is very easy in SSIS (maybe easier than in TSQL? :-P) using a very will hidden property:

http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/31/filenamecolumnname-property-flat-file-source-adapter-ssis-nugget.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Jeff Moden (12/4/2011)
deepakagarwalathome (12/4/2011)
I have never worked with SSIS but I need to work with it now!
If expert knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mino-bike through a match box. 😛
With that thought in mind and depending on the format of the files, this is all fairly easy to do in T-SQL. Are you sure you need to do it in SSIS? And, no... you certainly don't need a VB script for any of this. 😉

Jeff, stop dragging people away from SSIS! 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Koen Verbeeck (12/5/2011)
Jeff Moden (12/4/2011)
deepakagarwalathome (12/4/2011)
I have never worked with SSIS but I need to work with it now!
If expert knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mino-bike through a match box. 😛
With that thought in mind and depending on the format of the files, this is all fairly easy to do in T-SQL. Are you sure you need to do it in SSIS? And, no... you certainly don't need a VB script for any of this. 😉
Jeff, stop dragging people away from SSIS! 😀

Heh... Why? SSIS is a 4 letter word. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

deepakagarwalathome SSC Enthusiast Points: 166 More actions · Answer 5

Hi Koen

Thanks for the link - I did see that page and I tried to exactly what it said (it appear to be a simple thing to do even to me) but for some reason it didn't seem to work for me.

Can I ask you one question though - in doing what has been suggested on the page that you refer to, does the table need to have a column predefined as a placeholder for the source filename or a new column gets created in the table over and above the existing columns in the table?

I will try either way tomorrow and let you know. In fact I will send you my package, hopefully with both options and the output that I am getting.

Thanks again!

Best regards

Deepak Agarwal

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

It creates a new column in your dataflow with the name that you provided in that property.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP