Binary with XML into db using NS/filesystemwatcher?

Have a network share that will routinely get an xml file and a pdf dropped together. The xml file contains metadata about the pdf. Want to insert the various metadata elements in the xml file and the blob'd pdf as a varBinary into a table row as they are dropped.

Would like the Watcher to fire C# code to insert the xml elements and blob the pdf into the table (or can this be set up declaratively?)

Assuming not, best NS architecture to do this?

Thanks, Dan

Sorry, the SQL NS has absolutely nothing to do with this scenario.

Recommendation: use the FileSystemWatcher in a custom Windows Service.

Best XML structure for import into SQL 2005

Hi,

I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>

I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...

Thanks,
Drammy
bump|||

Drammy wrote:

Hi,

I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>

I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...

Thanks,
Drammy

If you are intending to update different tables with this, you won't be able to use a data flow. You could use the XML Task to read the XML and the Execute SQL to run a statement using the OPENXML function in SQL Server.

|||Thanks jwelch,

I actually ended up using a DataFlow pulling the XML data into a temp table and then run a SQL task to update the corresponding target tables.

Drammy
|||That's a good approach too

Best XML structure for import into SQL 2005

Hi,

I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>

I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...

Thanks,
Drammy
bump|||

Drammy wrote:

Hi,

I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>

I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...

Thanks,
Drammy

If you are intending to update different tables with this, you won't be able to use a data flow. You could use the XML Task to read the XML and the Execute SQL to run a statement using the OPENXML function in SQL Server.

|||Thanks jwelch,

I actually ended up using a DataFlow pulling the XML data into a temp table and then run a SQL task to update the corresponding target tables.

Drammy
|||That's a good approach too

Best way to update/insert/delete in sqlserver database

Hello
I've passed the last days going around with xml, sqlxml, uppdategrams,
diffgrams, transactions, sp's, everything, I suppose, but still have
not a clear line of thoughts...
Im new in the info systems world.. I'm currently developing an
windows app in vb.net running over sqlserver 2000. Till now I was
using simple inserts, updates and deletes in db tables (not much so
far) but now that performance and data consistency issues are being
discussed, I was trying to understand the best way to pass data
to/from database.
Clearly I want to have the business logic in server side, being
executed by storedprocedures, but in large updates, inserts and
deletes I must pass many data and that it's a problem.
For an update I was doing something like this:
Try
loTransaction = lcnPlada.BeginTransaction()
lcmActualiza.Transaction = loTransaction
For Each loLinha In tdtActualizaHistorico.Rows
lcmActualiza.CommandText = "insert into
stetiquetahistorico (encomenda,produto,tamanho,cor,etiqueta,etiquetaem issor,dataemissaoetiqueta,etiquetaqtd,ficheiroemis sao)
" & _
"values (" & CType(loLinha(0), Integer) &
"," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
Integer) & "," & tnNumeroEmissao + 1 & ")"
lcmActualiza.ExecuteNonQuery()
Next
loTransaction.Commit()
Return True
end try
But have logic (a for each) inside a transaction its not a good idea,
right?
I've seen many different examples of adodb objects constructing a
stream with the xml statements that executes a sp and maps the table
columns to perform updates or inserts and then
adodb.command.commandstream = stream
adodb.command.execute
and in server side creates a sp that
sp_xml_preparedocument
...
insert...
sp_xml_removedocument
Sqlserver books online has an example of it that works correct in
northwind database but what about using sqlcommand? or
sqlxmlcommand?
Can I use a sqlxmlcommand to execute a stream that was written in
code, instead of using templates?
Is there other ways than sqlxml to perform this kind of tasks?
Anybody can help me, please?
Thanks in advance
sp_xml_preparedocument/OpenXML is a T-SQL feature. So it is independent of
what client you are using to connect to the database. Steps are:
1. Write your stored proc to do the logic
2. Use the normal way that your client requires to pass arguments and
execute the stored proc.
You only need the command stream object if you are using FOR XML to get XML
back from the server.
HTH
Michael
"?scar Martins" <subdueme@.hotmail.com> wrote in message
news:229f0a23.0410141120.1efff3b8@.posting.google.c om...
> Hello
> I've passed the last days going around with xml, sqlxml, uppdategrams,
> diffgrams, transactions, sp's, everything, I suppose, but still have
> not a clear line of thoughts...
> Im new in the info systems world.. I'm currently developing an
> windows app in vb.net running over sqlserver 2000. Till now I was
> using simple inserts, updates and deletes in db tables (not much so
> far) but now that performance and data consistency issues are being
> discussed, I was trying to understand the best way to pass data
> to/from database.
> Clearly I want to have the business logic in server side, being
> executed by storedprocedures, but in large updates, inserts and
> deletes I must pass many data and that it's a problem.
> For an update I was doing something like this:
> Try
> loTransaction = lcnPlada.BeginTransaction()
> lcmActualiza.Transaction = loTransaction
> For Each loLinha In tdtActualizaHistorico.Rows
> lcmActualiza.CommandText = "insert into
> stetiquetahistorico
> (encomenda,produto,tamanho,cor,etiqueta,etiquetaem issor,dataemissaoetiqueta,etiquetaqtd,ficheiroemis sao)
> " & _
> "values (" & CType(loLinha(0), Integer) &
> "," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
> CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
> Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
> Integer) & "," & tnNumeroEmissao + 1 & ")"
> lcmActualiza.ExecuteNonQuery()
> Next
> loTransaction.Commit()
> Return True
> end try
> But have logic (a for each) inside a transaction its not a good idea,
> right?
>
> I've seen many different examples of adodb objects constructing a
> stream with the xml statements that executes a sp and maps the table
> columns to perform updates or inserts and then
> adodb.command.commandstream = stream
> adodb.command.execute
> and in server side creates a sp that
> sp_xml_preparedocument
> ...
> insert...
> sp_xml_removedocument
> Sqlserver books online has an example of it that works correct in
> northwind database but what about using sqlcommand? or
> sqlxmlcommand?
> Can I use a sqlxmlcommand to execute a stream that was written in
> code, instead of using templates?
> Is there other ways than sqlxml to perform this kind of tasks?
> Anybody can help me, please?
>
> Thanks in advance