Making Excel desination dynamic..

Question

Making Excel desination dynamic..

only4mithunc

SSCrazy Eights

Points: 8434
More actions
April 22, 2012 at 12:14 pm

#260297

Hi Friends
I have one requirement, I'm pulling data from OLEDB source and need to place in excel file, as you know this is simple but what I need is for each time when I execute the package I need to place the data to different path.
I have tried the below steps:
1)I have created the excel connection manager.
2)Created a package level variable and gave the path and file name same as the one created in the excel connection manager ( say c:\Folder1\Myfile.xls)
3) Executed the package I got the result in this path.
4) Before my 2nd execution of the package I have changed the path to c:\Folder2\Myfile.xls
But the problem is I'm getting the error as the file is not existing there, even though I have made the delay validation true Im getting this error. Any way to resolve this?
Thanks & Regards,
MC

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

If I'm not mistaken, the Excel file should already be created before you attempt to write data to it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Thanks Koen Verbeeck.

So you mean to say we cannot make it dynamic the way I required..? Is there any way you can suggest to achieve it?

Thanks & Regards,
MC

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 3

You can specify a dynamic destination in the File System task.

I found this after a quick Google; hope it helps.

http://sqlserversolutions.blogspot.co.uk/2009/04/copyrename-file-using-file-system-task.html

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 4

Just to back up Koen's comment, the file does need to exist first but once populated you can copy the file to another location as previously posted.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

+1 on the Excel file needing to exist first

When you consider that an Excel file is treated as a file-resident database (and not a simple text file) by SSIS (via the Excel provider) it is easy to make the leap that it must exist before you can write data to it...would you expect SSIS to create a new SQL Server Database for you on the fly just because you decided you wanted to write data to it 😀

What I often see, and have implemented many times myself, is a template-file (not an "Excel Template" which is a formal concept, just a representative Excel Workbook file) containing the required structure that is deployed with the SSIS package. Then, in the package, a File System Task that precedes the Data Flow Task copies the template-file into a new location with the desired name, and then the Data Flow task populates it as needed and the package carries on as needed.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

opc.three (4/23/2012)
What I often see, and have implemented many times myself, is a template-file (not an "Excel Template" which is a formal concept, just a representative Excel Workbook file) containing the required structure that is deployed with the SSIS package. Then, in the package, a File System Task that precedes the Data Flow Task copies the template-file into a new location with the desired name, and then the Data Flow task populates it as needed and the package carries on as needed.

+1 for this technique. It's used the most.

Nice database analogy by the way 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 7

Thank you all.

I have tried the option of copying the file ( with data ) to the new location using File System task, but I don't think it is the best option rather I have to try copying the template first and then extract the data to that location ( as give in the above reply )

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 8

Hi All,

I have tried 2 options

Option 1. After the DFT I have used a File System task to copy the file to the required destination location. This worked successfully but I doubt whether this is the best method.

Option 2. Created a template excel file and then used a File System Task before the DFT.

The file system task was success so that it has copied the file to the folder but the DTF failed giving an error as

" [Excel Destination [144]] Error: Opening a rowset for "BP" failed. Check that the object exists in the database."

Here BP is the Sheet name of the file.

I have made the delay validation true for the DFT.

Thanks & Regards,
MC

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

only4mithunc (4/24/2012)
Hi All,
I have tried 2 options
Option 1. After the DFT I have used a File System task to copy the file to the required destination location. This worked successfully but I doubt whether this is the best method.

It depends on what you're doing, but copying the file after export to a final location so no one can access it prematurely sounds like a fine idea for some environments.

Option 2. Created a template excel file and then used a File System Task before the DFT.
The file system task was success so that it has copied the file to the folder but the DTF failed giving an error as
" [Excel Destination [144]] Error: Opening a rowset for "BP" failed. Check that the object exists in the database."
Here BP is the Sheet name of the file.
I have made the delay validation true for the DFT.

How are you maintaining the name of the Excel file? In one of my packages I have a variable where I load the location and name from a database parameter table, and then I use that variable to set the Excel Connection as well as the File Connection I use in the File System Task to copy the template-file into place. Also, double-check that your template has a tab named BP.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 10

Hi opc.three,

Yes the template has the sheet with same name "BP"

I have tried the below steps for Option2

1. Created the file system task and pointed the source and destination location -- This works fine.

2. file system task from step1 is connected to DFT, inside this OLEDB source and Excel destination. Here the excel connection manager I'm configuring by pointing to the destination folder of step 1

Is something wrong in step 2 by which I'm getting the above error? I'm not using any variables as of now.

Thanks & Regards,
MC

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 11

Sounds like you have it setup correctly...but problems nonetheless. Attach your SSIS package to this thread, or send me a PM with the package and I'll be happy to have a look.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 12

Hi opc.Three,

I have sent you a PM with the package ( entire solution ) I'm attaching the same here again.

The solution has two packages, Package1 is where I have tried the option of copying the files to different folder using filesystem task and it is working fine.

Package2 is the one where I have tried to keep the template and copy it to the destination before the data extracts to the file and this wasn't success.

Thanks & Regards,
MC

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 13

Sorry, my VS2008 is acting up and I am getting the dreaded "Unspecified error" when trying to access a mocked up Excel file to make your examples work. The examples I have that use the technique we're discussing are all still on SQL 2005 and they are setup exactly as you have your Package2 setup, I verified one of them from my production environment.

I have not seen the technique you used in Package1 in a production setting, but it should achieve the same result, albeit with a bit more code...but hey, if that technique works for you I would have no qualms about moving forward with it. What you have done there really drives the point home about how the Excel provider offers up a database-like experience.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 14

Thanks for trying it.

Why I'm not happy with Option 1 because here we are copying the entire data after extracting so performance point of view I think Option 2 is better.

Thanks & Regards,
MC