Capturing XML

Ok, this is a broad question. Our system is going to receive xml files from vendors that contain background check results for individuals. We want to capture the xml results in the db for historical purposes and to have a copy of what the vendor sent before we translated the results and updated our system. I was going to store the xml in a table with a text column but wasn't sure if there was a better way to do this. Anyone have any suggestions?

ThanksI think there have a couple of posts recently on this very subject. I think the general consensus is to store the XML in an NText or Text column. Individual contributors have cautioned that these column types require additional "attention" in the form of more frequently scheduled DBCC CHECKDB statements. That has not been my experience, but others have suggested it.

Certainly in your case, since you want to keep a copy of the data received prior to manipulating it, storing it "as-is" is probably a good idea. An alternative might be to store the filename (as a pointer) in the database while saving the XML to a file on either the database server or some other location.

Regards,

hmscott|||Well, you're going to need to use the data too, right?

DECLARE @.xml varchar(1000)

SET @.xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
<InsertSessionData>
<Input>
<SessionID>6F9619FF-8B86-D011-B42D-0C04FC964FF</SessionID>
<Last_Accessed>20030101</Last_Accessed>
<State><somexml></somexml></State>
</Input>
</InsertSessionData>'

DECLARE @.idoc int

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml

SELECT * FROM OPENXML(@.idoc, '/InsertSessionData/Input', 2)
WITH(
SessionID varchar(8000),
Last_Accessed Datetime,
State Text
)|||Brett,

Your point is well taken. However, there are other ways of getting at the data in the documents. We use a VBScript and the MS DOM to strip out the necessary data and store it in the relational tables. We don't keep a copy of the XML docs (no requirement to do so), but if I did, I could simply add a line to insert the text into an archive table for historical purposes.

But I don't use OPENXML to parse the XML itself.

Regards,

hmscott|||Thanks for the ideas. We're going to extract the data we need through java code. The table is strictly for archiving. Kind of an insurance policy if we have problems with our interface application.

Capture date and timestamp of a file?

Hi,

I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.

Any ideas?

Thanks in advance for your help.

$wapnil

Doesn't the transfer of files from the FTP server preserve the date and time? You could inspect that with a custom script.|||

No the transfer of files do not preserve the date and time. When i download the files all the files show the same date and timestamp.

Can the FTP task be configured to preserve the date and time?

Thanks!

$wapnil

|||

spattewar wrote:

No the transfer of files do not preserve the date and time. When i download the files all the files show the same date and timestamp.

Can the FTP task be configured to preserve the date and time?

Thanks!

$wapnil

Well.... You can write a custom script to perhaps issue an 'ls' command inside the FTP session. Remember, FTP isn't designed for file interrogation, it's designed to transport files.|||

Thanks.

I am using a ForEach loop container which iterates through a list of file names to be downloaded. In this container I have an FTP task that uses an FTP connection manger to connect and download the file name passed to it by the container. If the FTP is successful then I update the database using an Execute SQL task to tell that the file has been downloaded.

Where should I keep the custom script so that it runs inside the FTP session? Also can I use the File System task in some way? I am pretty new to his and hence may be asking very rudimentary questions..please be patient.

Thanks agains for your time.

$wapnil

|||The script would be your FTP session. It would replace your FTP task.|||

I will try this out and will let you know in case there are any problems.

Thanks.

$wapnil

Can't use Package Variable to specify the filename for a Connection

I'm attempting to modify a flatfile import package so that I can reuse it to import several other files into the same table. I was unable to use package variables (containing the import filename) in the Flat File Connection Manager file name field. What is the proper way to solve this kind of problem in SSIS?Have you looked up property expressions in BOL? Give that a look.
http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/12/7750.aspx
Thanks,
K|||

I setup a ForEach Loop Container to do this.

Create a Package Variable, for example, "varFileName", for the FileName. Then edit the ForEach Loop container. Select "Variable Mappings". Select the drop-down under the "Variable" column and select your "User: varFileName" variable. The index column should read "0". Save the object. Now add a Data Flow task and double-click to edit.

In the Data Flow window, create a Source Script transformation. Define your output columns on the "Inputs and Outputs" option. Click "Script" and add your variable "varFileName" to the property "ReadOnlyVariables". Now click the "Design Script" button, which opens up another window for scripting.

Add "Imports System.IO" to the top so you can read the flat file.

Read the file with the following code:

Dim strRecord as String
Dim objFlatFile As StreamReader = File.OpenText(Variables.varFileName)
Do Until objFlatFile.Peek = -1
strRecord = objFlatFile.ReadLine
[OutputBuffer].AddRow()
[OutputBuffer].[Column1] = Mid$(stRecord,1,4)
... etc.
Loop

Let me know if this helps...