Can't pass param/variable from ForEach Loop to Execute SQL Task
I am trying to bulk load XML files from a folder into a table with an XML data type column using SSIS 2005. I can't get the Execute SQL Task to "see" the user variable containing the file names that the ForEach Loop walks through. I called it @[User::strFilename] and when that didn't work tried mapping that as parameter paraFile. I've tried the connector as ADO.NET (using named parameters) and as OLE DB (using ?s). You can see the bulk load command I'm using for the Execute SQL Task within the full error message pasted below. I also tried the SSIS Bulk Load tool, but couldn't get it to pull the XML files into the XML column in the table. Please help!? SSIS package "Grab.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query " DECLARE @xmlFileName VARCHAR(300), @x xml select @xmlFileName = paraFile OPENROWSET EXEC('INSERT INTO dbo.XML(xmlFileName, xml_data) SELECT ''' + @xmlFileName + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ') " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Grab: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Grab.dtsx" finished: Failure.
May 31st, 2011 10:25pm

I hope you made sure you capture the file name to load, then the following is the correct mapping scehma: Connection type Parameter marker ADO ? ADO.NET and SQLMOBILE @<parameter name> ODBC ? EXCEL and OLE DB ? so e.g. for ADO.Net you supposed to use @paraFile. Also, the @x xml was never used, remove it. Try the above and if that does not work then the answer is in how you mapped the parameters.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2011 10:41pm

Check that your User Variable has the correct scope i.e. ensure it is set to Package. Additional reading - http://msdn.microsoft.com/en-us/library/ms141085(v=SQL.90).aspxJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
June 1st, 2011 12:00am

Thanks Arthur, I tried it again with ADO.NET and named parameter @paraFile. Now I get a different error (see below). The parameter's data type is set as String. SSIS package "Grab.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @xmlFileName VARCHAR(300) select @xmlFileName = @paraFile OPENROWSET EXEC('INSERT INTO dbo.XMLX(xmlFileName, xml_data) SELECT ''' + @xmlFileName + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ') " failed with the following error: "Incorrect syntax near the keyword 'OPENROWSET'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Grab: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Grab.dtsx" finished: Failure.
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2011 12:10am

Here is what I tried and it works fine: 1. In the ForEach Loop task, select 'Fully qualified' for the Retrieve File name option under Collection. 2. Pass the variable into a SQL task with the following code: declare @sql varchar(1000), @fq varchar(260) select @fq = ? select @sql = 'SELECT ''' + @fq + ''', * FROM OPENROWSET(BULK ''' + @fq + ''',SINGLE_BLOB) AS q' insert into x (a, d) exec(@sql) Map the parameter for the position 0 so that ? gets the fully qualified file name during execution. N.B.: Modify the insert statement according to your table-column names. HTH.
June 1st, 2011 2:36am

Thanks very much to all of you for your help! Especially Niropam as it worked perfectly after I did exactly as he suggested above.
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2011 9:40pm

No problem and thanks for marking your question answeredJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
June 1st, 2011 9:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics