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 2012 Forums
 SSIS and Import/Export (2012)
 writing back data from Table to Excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divanu
Starting Member

6 Posts

Posted - 04/10/2013 :  11:03:48  Show Profile  Reply with Quote

HI,

I am trying to load the data from table to Excel files.
I have 5 folders, each folder has an exel file called "Excel Upload.xlsx".

I need to write data into each of the excel file(into one of the sheet) based on one condition.

If the prefix of one of the ID in the table matches prefix of folder name then write that data back to that particular folder.
Ex: ID: "GARNIER SHAMPOO" then write back to \\servername\e$\Data\MDP_76_02\Inbound\Excel Upload\Garnier\excel upload.xlsx

I am facing issues while doing this, Can any one please provide me an approach.

divanu
Starting Member

6 Posts

Posted - 04/10/2013 :  14:30:38  Show Profile  Reply with Quote
I don't want to hard code the prefixes. I would like to get the folder prefixes dynamically and match to prefix of the ID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/11/2013 :  02:41:36  Show Profile  Reply with Quote
in that case you need to have a mapping table which holds the folder names against the prefixes. then in your data flow task you can check for the prefix and based on that you can set value of variable you creating for holding the corresponding excel path. Then in excel destination map this variable to connection string property for pointing to correct excel.

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

divanu
Starting Member

6 Posts

Posted - 04/11/2013 :  13:20:52  Show Profile  Reply with Quote
Thanks for the reply. But I don't want to create a mapping table.

Can you please tell me how can i get folder name dynamically.

I have a foreach loop container. The loop has "dirpath" in expression and excel file in variable mapping
Inside foreach loop container I have a Dataflow Task
The Dataflow task has a OLEDBSource(for getting recordset) and Excel Destination.I have a conditionalsplit inside the DataFlow Task, The conditional split has substring(Id,1,3) == SUBSTRING(@[User::strFolderName],1,3)
The problem is I am not getting the folder name dynamically.
Go to Top of Page

divanu
Starting Member

6 Posts

Posted - 04/11/2013 :  13:22:33  Show Profile  Reply with Quote
This is kind of urgent. I really appreciate your input.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/12/2013 :  04:44:30  Show Profile  Reply with Quote
quote:
Originally posted by divanu

Thanks for the reply. But I don't want to create a mapping table.

Can you please tell me how can i get folder name dynamically.

I have a foreach loop container. The loop has "dirpath" in expression and excel file in variable mapping
Inside foreach loop container I have a Dataflow Task
The Dataflow task has a OLEDBSource(for getting recordset) and Excel Destination.I have a conditionalsplit inside the DataFlow Task, The conditional split has substring(Id,1,3) == SUBSTRING(@[User::strFolderName],1,3)
The problem is I am not getting the folder name dynamically.


In Excel conection manager set connection string property to variable where you set path. Make this variable dynamic (evaluate as expression property to true) and give an expression to append the folder name variable value to the rest of the path

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

divanu
Starting Member

6 Posts

Posted - 04/12/2013 :  12:02:02  Show Profile  Reply with Quote
Thank you for the quick reply. I have done that, Still Its not getting the foldername dynamically. Its not getting the entire path and its not looping through subfolders.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/12/2013 :  14:21:11  Show Profile  Reply with Quote
hmm...how have you set the path expression? inside foreachloop what option you chose? fully qualified?

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

divanu
Starting Member

6 Posts

Posted - 04/12/2013 :  16:42:55  Show Profile  Reply with Quote
I am trying to attach the package here. But I don't see options here.
Inside the Foreach loop, I have selected 'Fully Qualified' option and 'traverse subfolders' option checked, under files i mentioned *.*
Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/13/2013 :  01:47:38  Show Profile  Reply with Quote
then it should give you full path.
then you've to apply some logic based on substring to add the foldername in between

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

jason663
Starting Member

3 Posts

Posted - 07/25/2013 :  08:08:57  Show Profile  Reply with Quote
Here is a library can write to Excel without Excel being installed.

https://www.kellermansoftware.com/p-52-excel-reports.aspx
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.14 seconds. Powered By: Snitz Forums 2000