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)
 Different excel file according Country

Author  Topic 

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2013-04-11 : 08:59:06
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

52326 Posts

Posted - 2013-04-12 : 04:58:51
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

354 Posts

Posted - 2013-04-12 : 09:03:51
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

354 Posts

Posted - 2013-04-12 : 09:22:53
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

52326 Posts

Posted - 2013-04-12 : 14:25:54
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
   

- Advertisement -