April 9, 2010 at 7:02 am
I have an existing DTS package that used nothing but VBScript to instantiate Excel, and copy every 1000 records from it into a numbered spreadsheet with specific column headings that don't necessarily match the view column names. As this ran on SQL 2000, and needs to now exist in SQL 2008, where Excel will NOT be allowed on the server (nor any other executable or dll that's not part of SQL Server or the operating system), I have to re-architect this into SSIS. I have no difficulties with the T-SQL parts, but I can't seem to figure out how to get the result set in my ForEach Loop Container's package variable to become a source for the Excel destination. I'm quite sure I'm probably going about this the wrong way, and unfortunately, I have almost no experience with SSIS and have always had to import or export data on an adhoc basis as opposed to having to use a DTS or SSIS package. I can easily generate the file path information from the T-SQL, but I just can't figure out how to translate that into a dynamic file path / filename setup, or even figure out how to source the Excel destination. Any/all help would be most appreciated.
Thanks!
Steve
(aka sgmunson)
:w00t::w00t::w00t:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 9, 2010 at 8:35 am
A whole lot of text, but I believe your question is how to get the result set from the T-SQL into the Excel, right?
You can map the resultset from the T-SQL into a variable of type object. (sort of like a recordset destination). The only problem is you can't really use it as a source, but you can loop over the variable with a for each loop container using ADO.NET.
In this for each loop, place a data flow task that will write each row to the Excel. If you are uncomfortable with opening so many connections to the Excel for just writing one single row at the time, write to a temp table instead. Then use the temp table as a source to write all the data to Excel in one single time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply