xml task in SSIS

Question

xml task in SSIS

washawgolla

SSC Eights!

Points: 841
More actions
June 9, 2009 at 4:59 pm

#155809

I have a DTS package which imports xml data into a table; I have vb script in ActiveX script to accomplish this
see sample code
Dim objBL
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=server1\instance1;database=databse1;uid=uid;pwd=pwd"
objBL.KeepIdentity = False
objBL.ErrorLogFile = "C:\folder1\Error.LOG"
objBL.Execute "C:\folder1\xmlmap\file.XML", "C:\folder2\xmldata\file2.XML"
where file.xml is xml file for the table and file2 is the xml data to be inported;
can we implement the say process in SSIS; if we have the mapping and data xml files; if yes how
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

washawgolla (6/9/2009)
I have a DTS package which imports xml data into a table; I have vb script in ActiveX script to accomplish this
see sample code
Dim objBL
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=server1\instance1;database=databse1;uid=uid;pwd=pwd"
objBL.KeepIdentity = False
objBL.ErrorLogFile = "C:\folder1\Error.LOG"
objBL.Execute "C:\folder1\xmlmap\file.XML", "C:\folder2\xmldata\file2.XML"
where file.xml is xml file for the table and file2 is the xml data to be inported;
can we implement the say process in SSIS; if we have the mapping and data xml files; if yes how
Thanks

It will not be easy to use SQLXMLBulkLoad object in SSIS. You can check this discussion for more information why. What you can is try to use the standard components available in SSIS. You have to:

1. Insert data flow task.

2. Go to data flow task canvas.

3. Insert XML Source component and point it to your source XML document.

4. Insert SQL Server Destination (or OLE DB Destination).

5. Connect the XML Source to the destination.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

washawgolla SSC Eights! Points: 841 More actions · Answer 2

thank you cozysoc;

what if I have multiple xml files

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

washawgolla (6/10/2009)
thank you cozysoc;
what if I have multiple xml files

If the XML format is same then you can setup the XML source to be configurable and use different source file. If the format is different, then you have to create separate data flow for each and every XML format you need.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

washawgolla SSC Eights! Points: 841 More actions · Answer 4

THant was a helpful response cosyRoc; I am having this problem to import those xml files to a table using SSIS

[SQL Server Destination [2984]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "server1\instance1.databse1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

washawgolla (6/11/2009)
THant was a helpful response cosyRoc; I am having this problem to import those xml files to a table using SSIS
[SQL Server Destination [2984]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "server1\instance1.databse1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Review your connection manager. Make sure you "Test" the connection from the setup dialog.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

w.w. SSC-Addicted Points: 412 More actions · Answer 6

how can I use "XML file from variable" as I have a list of xml files? I use Foreach Loop container and able to get full path of xml file, but when I set Data Access mode to XML file from variable, I have an errors:

"XML Source (228) was unable to read the XML data." It seems that I have to do this at run time in Design Script.

jeevan.jeev1 SSC Enthusiast Points: 122 More actions · Answer 7

Hi,

i am having 15 xml files with the same format in it but with different data in them, i have to move all xml files in to the 15 tables in sql server 2008, using the foreach loop to do the task but stuck up.can you please explain briefly how to import multiple xml files to sql database using ssis, i am using sql server 2008