Different excel file according Country

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

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.

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources