Post reply

Can the ForEachFile enumerator value be passed to SQL task query??

  • September 18, 2006 at 8:10 am

    #153189

    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

    #661088

    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

    #661102

    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

    #661143

    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://qa.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