SQLXMLBULK load in SSIS - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SQLXMLBULK load in SSIS

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-31 : 16:30:51

Greetings

How can I implement this sample code in SSIS using Script Task C#?

// Create a new thread
Thread bulkLoad = new Thread( new ThreadStart( LoadData ) );
bulkLoad.ApartmentState = ApartmentState.STA;
bulkLoad.Start();

// Load data
public void LoadData()
{
// Create new bulk load object SQLXMLBulkLoad3Class
objXBL = new SQLXMLBulkLoad3Class();
// Set connection string
objXBL.ConnectionString = "..."
// Set log file
objXBL.ErrorLogFile = ".."
// Set keep identity to false
objXBL.KeepIdentity = false;
// Execute bulk load
objXBL.Execute("Books.xsd", "Books1.xml");
}

Thanks

If you don't have the passion to help people, you have no passion

latch
Yak Posting Veteran

62 Posts

Posted - 2011-06-01 : 10:36:18

Hi,

In script task you need to add few references :
1. Using System.Threading and modify the code as:

Thread bulkLoad = new Thread( new ThreadStart( LoadData ) );
bulkLoad.ApartmentState = ApartmentState.STA;
bulkLoad.Start();

2. From Project menu, select Add Reference.

In the COM tab, select Microsoft SQLXML Bulkload 3.0 Type Library (xblkld3.dll) and click OK. You will see the Interop.SQLXMLBULKLOADLib assembly created in the project.

Replace the Main() method with the following code. Update the ConnectionString property and the file path to the schema and data files.
[STAThread]
static void Main(string[] args)
{
try
{
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
objBL.ConnectionString = "Provider=sqloledb;server=server;database=databaseName;integrated security=SSPI";
objBL.ErrorLogFile = "error.xml";
objBL.KeepIdentity = false;
objBL.Execute ("schema.xml","data.xml");
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
}

Source:
http://msdn.microsoft.com/en-us/library/aa257393(v=sql.80).aspx

I didn't tried these. But i think we made the changes try once.Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-02 : 12:25:26

cool. I got it now. I just could not find that dll it was located under C:\Program Files\Common Files\System\Ole DB\xblkld4.dll. Because I do not see Microsoft SQLXML Bulkload 3.0 Type Library in the COM tab

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources