Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SSIS_Beginner
Starting Member
4 Posts |
 Posted - 2013-02-15 : 14:53:40
|
| I am having issues refining a query within a SSIS “Execute SQL” task and was wondering if anyone could help. The ultimate goal is to take an XML file content from directory and load it into a SQL DB table along with the file name and date. I have a for-each loop container to loop through the directory with a variable to hold the file name. In my “Execute SQL” task I have 3 parameters set up – “filename, Path, filename” consecutively numbered from 0 to 2 in the Parameter name fields. When I add the 2nd and 3rd question mark for the path and filename I get the error: "Multiple-step OLE DB operation generated errors” – here is my query – INSERT INTO ifo_xml(xml_file_name, xml_contents, load_time)SELECT '?', xmlData, getdate() FROM(SELECT * FROM OPENROWSET (BULK ''?'+'?'',SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)Here is the catch!I can run the query below successfully when I use the first variable question mark and hard code the path with the files name. So otherwise the bulk load is successful and the columns in the DB table are correct. INSERT INTO ifo_xml(xml_file_name, xml_contents, load_time)SELECT '?', xmlData, getdate() FROM(SELECT * FROM OPENROWSET (‘\\myserver\ filemname.xml’ ,SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA) When I add the additional question marks to substitute for the path and filename I get the error. Any help would be appreciated. SSIS_Beginner |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 14:59:08
|
| whats the datatype you used for those variables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
SSIS_Beginner
Starting Member
4 Posts |
Posted - 2013-02-19 : 17:38:37
|
| I have tried both NVARCHAR and VARCHAR for the variables. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 01:39:10
|
| why not pass the entire filename with path through a single variable? Add a variable for full path in SSIS, make EvaluateExpression property true and give value as path variable + filename variable. then use this new fullpath variable to substitute for single ? after BULK clause------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
SSIS_Beginner
Starting Member
4 Posts |
Posted - 2013-02-20 : 11:26:35
|
| I did put the path and file name variable in an expression and use it in the query - I received the same error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." - below is the query used: INSERT INTO ifo_xml(xml_file_name, xml_contents, load_time)SELECT '?', xmlData, getdate() FROM (SELECT * FROM OPENROWSET (BULK ''?'' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)Any other thoughts? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 13:18:41
|
| can you try putting whole query inside variable by using expression builder and then use variable option in execute sql task?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
SSIS_Beginner
Starting Member
4 Posts |
Posted - 2013-02-22 : 09:48:36
|
| Strangely I found that this works:EXEC ('INSERT INTO ifo_xml(xml_file_name, xml_contents, load_time)SELECT ''' + ? + ''', xmlData, getdate()FROM(SELECT * FROM OPENROWSET (BULK ''' + ? + ? + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)')I am not sure why this works and not my previous code that is similar. thanks for your suggestions. |
|
|
|
|
|
|
|
|
SSIS - Variable Substitution