I'm trying to write an SSIS script that will create an excel file and append data to it.
I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.
However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.
If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.
Am I missing anything obvious?
Thanks for your help in advance
Mike
Do you have the Visual Studio Tools for Office 2007 installed?
Just curious, why create the excel file in the script task?
|||
Mike_Gaziotis wrote:
Hi guys, I'm trying to write an SSIS script that will create an excel file and append data to it.
I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.
However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.
If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.
Am I missing anything obvious?
Thanks for your help in advance
Mike
This should help.
VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx)
-Jamie
|||The VSA designer's "Add Reference" dialog does not look for assemblies in the GAC but only in the %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies and %windir%\Microsoft.NET\Framework\v2.0.50727
We recomend that you copy them to the SQL server location pointed above for the "Add Reference" to work.
|||Thanks very much Jamie and Silviu!Putting the DLLs in the .NET Folder seems to have done the trick.
In response to jwelch's question: I would have used the standard Data Flow task but it didn't seem to support some of the functionality i needed (ie dynamically building the file path and file name based on content etc)
Thanks again guys
Mike
|||
Actually, that sounds like a great opportunity to use variables or expressions. If you describe your scenario a little more, perhaps there might be some alternative approaches.
Not trying to change your mind on direction, just want to make sure you know what options are available.
|||Hi Jwelch,Always keen to find out if there are more approaches that i haven't considered. Even if they're not used in this scenario it's good to know of the possibilites
Here are my SSIS job requirements:
- Execute (several) SQL queries which retrieve report data
- Insert the data in worksheets of several excel files
- Save the files by:
a: Putting a timestapt in their name (ie reportA_04072007)
b: storing them in different locations on the server based on the report
(ie %Reports%/ReportA/reportA_04072007.xls)
- Send mails with excel files attached
This is to be a scheduled job managed by the SQL server agent.
It's not a job of enormous complexity i must admit, but i somehow i found myself (perhaps because of me being a developer rather than a DBA by nature) more keen to write code than having to use loops, data flow tasks and all the rest. In fact when i first had the problem with the assemblies i half considered writing a windows app that would do that job and then run that from the sql server agent. And really, is there such a big difference between writing an app or a script-only SSIS job?
Thanks and sorry for the babble
Mike
