Loading the 100 files(.csv) from folder along with

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Loading the 100 files(.csv) from folder along with

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-09-27 : 00:14:30

Dear All,

I have 100 number of .csv files where i am using data transformation task to load the data to database of SQL server 2005 manually/

I was manually running this but it was too much time consuming,could you pls help me in making in the for loop task.

Where i need to capture the file from folder and insert that file name to DB column name.

Any link or reference or guidance will be helpful.

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 00:56:22

you can use ForEachLoop task in SSIS for achieving this. Use ForEachFileEnumerator inside it and point to your folder. Then add a variable inside SSIS to retrive the file name during each iteration. Add a data flow task inside and use variable to map value to connection string property of excel source task added inside using expression builder. then connect to OLEDB destination to dump data to your table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-09-27 : 02:00:45

Hi
thanks for this

I have used for each loop task and configured to loop through the files and its working very fine now.
I have used variable which is pointing to Folder but the file name which i getting inserted is

E:\temp\PB\SR182510 PBS Item Data for January 1992.csv

But i need to insert file name as only January 1992 to column in DB.

Could u pls help me in this regard.

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 04:42:10

choose filename only option in for each loop. add a new variable and put evaluate as expression true for it. In expression tab use an expression based on Substring on already created variable @[User::Filename] to get only last part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-09-27 : 04:54:31

Hi,

I am not clear what u r trying to say.

Thanks,
Gangadhara

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 05:17:22

did you have a look at properties of for each loop you've added. in that there's an option to select how to return name of file retrieved(fully qualified path,filename & extension, filename only). in that select filename only.
then add a new variable to package and in variable properties for it set evaluate as expression property as true. then in expression property set expression as below

REVERSE(SUBSTRING(REVERSE(@[User::FileName]),1,FINDSTRING(REVERSE(@[User::FileName])," ",2)-1))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-09-27 : 05:32:54

Though i have declared the new variable at package level it say variable not exist

Pls suggest

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 06:05:49

you've not created it in package scope. check scope of variable in variable tab

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources