[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting the file names each it time it loop through the file into the column name in ssis

Posted on 2011-10-19
4
Medium Priority
?
406 Views
Last Modified: 2013-11-10
I am trying to create an ssis package trying to pull the data from teh csv files into the oracle tables.




I am trying to get 6 files from the local C drive and it has files such as
 

Ohio_tp_data_inlab2.et.ky.micro.com_2011-10-18.csv


Ohio_tp_data_loulab2.et.ky.micro.com_2011-10-18.csv


Ohio_tp_data_emrvill2.et.ky.micro.com_2011-10-18.csv


Ohio_tp_data_crrwo12.et.ky.micro.com_2011-10-18.csv



Ohio_tp_data_logicall2.et.ky.micro.com_2011-10-18.csv


what i need to do is i need to loop through the files and get teh file name into the variable each time we loop through the file and get the column name into one of the "TargetSource" field that I have in the table.
So each time I looop through the data from the csv file I need to get the filename into the column along with the data.

and then the second file .... had to do the same thing get teh file name along with other column values and so on...

Like wise for all teh 6 files.  

So that when the data is loaded into the table I can check using the source which represents the filename.

0
Comment
Question by:srionline2k6
  • 2
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36997805
0
 

Author Comment

by:srionline2k6
ID: 37009007
Thank you Patel...I am still not able to figure out wher I am going wrong.

This is what I did:

Please correct me where I’m going wrong:
1   I dragged a forloop task and configured the enumerator as following:

 
2 file
Then I did the variable mapping as follows:  This variable is used for getting the file name and also connecting it to the connection manager (Correct me if I wrong)
Q: What this variable actually do ?  I guess it will grab the filename (Correct me if Iam wrong)


3rd file Note: But This variable is also used in mapping the flat file directory to the connection manager passing folder dynamically.
For dynamically passing the flat file connection I am connecting the connection manager to the variable  I have declared  a variable for receiving the file ( I have a doubt do we need to pass the same variable that I am using it to collect the file name


4th file:

I put the Dataviewer in the dataflow task after the derived column where I am pulling the filename
 


Finally the package is failing because of Null constraint as the table doesn’t permit null values.



     Forloop variable mapping variable mapping1-For-loop.png
3-Propertyexpressions.png
0
 

Author Comment

by:srionline2k6
ID: 37009048
Sorry THe above one is submitted incompletely not in a proper way

Thanks Patel !!

This is what I did:

Please correct me where I’m going wrong:
1   I dragged a forloop task and configured the enumerator as following:

 
2 file
Then I did the variable mapping as follows:  This variable is used for getting the file name and also connecting it to the connection manager (Correct me if I wrong)
Q: What this variable actually do ?  I guess it will grab the filename (Correct me if Iam wrong)


3rd file Note: But This variable is also used in mapping the flat file directory to the connection manager passing folder dynamically.
For dynamically passing the flat file connection I am connecting the connection manager to the variable  I have declared  a variable for receiving the file ( I have a doubt do we need to pass the same variable that I am using it to collect the file name


4th file:

I put the Dataviewer in the dataflow task after the derived column where I am pulling the filename
 


Finally the package is failing because of Null constraint as the table doesn’t permit null values. Forloop variable mapping Propertyexpressions Derived column for getting the filename\


Help me where I am going wrong. What will be the suggestions?

Thanks
5-dataviewer.png
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 2000 total points
ID: 37022406
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

864 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