SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Different excel file according Country
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 04/11/2013 :  08:59:06  Show Profile  Reply with Quote
Hi All

I am struggling with my requirement where I need to load the data in diff excel files according country


CountryName  code
USA           01
India         91
SA            27


The above table is only sample data of my requirement..
I want All US data Should be loaded in USA.xls
All India Data Should be loaded in India.xls
All SA data should be loaded in SA.xls

I hope you understand my requirement ..Please let me know any
link where this example can be found

Thanks






Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/12/2013 :  04:58:51  Show Profile  Reply with Quote
You can use a for each loop for this. Use a EXECUTE SQL TASK to load an object type variable Countries Created in SSIS with your country names from table.
Use For Each Loop over this variable to loop through countries and add another variable CountryName to hold country name for each iteration
Then add a variable SQLQUery for forming dynamic query and put value as "SELECT columns.. FROM YourTable WHERE CountryName = '" + @[User::CountryName] + "'"
Inside for each loop add a data flow task. Add a OLEDBSource to connect to your database and choose sql command from variable option. Map the variable as SQLQuery
Add a variable ExeclConnection and make value as <YourPath value> + "\" + @[User::CountryName] + ".xls"
Then add a excel destination and map columns to it. Add an expression for connectionstring property of excel and map it to ExcelConnection variable created above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 04/12/2013 :  09:03:51  Show Profile  Reply with Quote
Thanks for your reply ..
I followed all steps as you mentioned but I am getting error
Error: Variable "User::Result" does not contain a valid data object

this error coming on ForEachloop ... this User::Result variable is a first variable which hold the result of my below query
Select distinct CountryName from country

I have defined this variable as Object .

I have used User::Result variable in ForEachloop container in Collection PAGE ... Here I used Foreach ADO Enumerator as a Enumerator
and selected User::Result variable in ADO object source variable in drop down list...

and then I created new variable User::CountryName as you mentioned .
data type of this var is string ...and index value is 0..

after doing above steps created third var. SQLQuery which is dynamic ..I am sure that there is no problem in third step...but error in coming in ForEach container..

Please suggest if you could...




Vijay is here to learn something from you guys.
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 04/12/2013 :  09:22:53  Show Profile  Reply with Quote
It is working fine..I was using SQL Execute task inside the ForEachloop
which was wrong ...Now I am using SQL Execute task outside of ForEachloop its working fine..

Thanks for your help..

Vijay is here to learn something from you guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/12/2013 :  14:25:54  Show Profile  Reply with Quote
cool
glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000