September 18, 2006 at 8:10 am
Theoretically it is possible to:
1) Loop through certain directories on the disk to read flat files with given extension using Foreach Loop container (and a Data Flow Task in it) and to assign the file name (the container's enumerator) to a variable like this User::varSomething
2) To include an Execute SQL task (in the SAME container) where to use the above variable to say, save in a table (someTable) the name of the file being read in the current iteration, e.g.
INSERT INTO someTable VALUES(?)
Here ? refers to a parameter (defined with "name" 0 since this is OLE DB connector) matched to the variable User::varSomething in the "Parameter mapping" section of the Execute SQL task.
But it doesn't work - "Unspecific error...permission denied or ..."
I desperately need this because the number identifying the data is included in the file name and NOWHERE ELSE.
September 18, 2006 at 11:07 am
Silly question, but: what is the SQL task you are executing? Is it an SP? Do you have permissions to execute the SP, or insert into the table?
September 18, 2006 at 12:03 pm
I am inserting some values from the files I read into a table - but this works. Where the SQL task breaks is when I want to get my grip on the user variable containing the file name and insert that one into the table, too.
What's funny is that when I added a Script task to the same Foreach Loop Container which just prints the variable:
Imports System.IO
Public Class ScriptMain
Public Sub Main()
MsgBox(Dts.Variables("varFileName").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
it worked. The question is how to get the value from varFileName into my query?
September 18, 2006 at 5:12 pm
Well, I am retracting my claim ... The schema described above works - I was just mixing statements which work in a sql script but are not quite legal in a query. A bit more SQL tasks and temp tables and everything is fine.
Plus, I discovered the question was recently discussed on this forum quite well :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=284755
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply