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