Empty Column: xml

Showing posts with label xml. Show all posts

Tuesday, March 27, 2012

Encryption question

Hello Everybody,
I have a Encryption - Decription Question.
In my project we are getting an XML File from a vendor which has a credit
card number in clear text. We use XML bulk load process to load table from
xml file.
How can i encrypt credit card number while storing into table.
Also i will have to decrypt CC number while i create comma separated file
for another vendor ?
Pls let me know.
thxA credit card number is typically not the type of attribute used for
indexing, sorting, grouping, or composing a primary key, so I see no reason
why it would not be a good candidate for encryption. It would be reasonable
and fairly simple to store it encrypted and decrypt it at the application
level only when needed for display on a customer information form or to
complete a business transaction.
However, a social security number is a different issue.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||I have a few line sof VB.Net code that Encrypts or Decrypts a string passed
to it; If interested then let me know and I will post the code for you.
"mvp" wrote:

> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||Thanks for the reply
But can i know how can i do this encryption-decryption..
thanks
"JT" wrote:

> A credit card number is typically not the type of attribute used for
> indexing, sorting, grouping, or composing a primary key, so I see no reaso
n
> why it would not be a good candidate for encryption. It would be reasonabl
e
> and fairly simple to store it encrypted and decrypt it at the application
> level only when needed for display on a customer information form or to
> complete a business transaction.
> However, a social security number is a different issue.
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
>
>|||I want to read from XML file (which has CC number in clear text, i use sql
bulk load to load xml into table) and load into sql server table in encrypt
form and then decrypt again when i create comma seperated file from table
again to provide feed to another vendor ?
Let me know, do u have similar thing ?
"Shariq" wrote:
> I have a few line sof VB.Net code that Encrypts or Decrypts a string passe
d
> to it; If interested then let me know and I will post the code for you.
> "mvp" wrote:
>|||If your entire input and output processing is done with SQL server then I
haven't done similar thing but you could possibly use ActiveX script to
Encrypt/Decrypt.
In a similiar type of situation when I had to encrypt/decrypt for input and
output; I use VB.Net application to re-process XML file as input, encrypted
the Credit Card numbers and output to an XML file. Then I executed a DTS
package to upload the XML formatted output file to a SQL Server.
When sending data to a client with decrypted CC numbers; I also used the VB
application to rad data from the SQL Server and during the data read,
decrypted the CC numbers and produced XML file.
"mvp" wrote:
> I want to read from XML file (which has CC number in clear text, i use sq
l
> bulk load to load xml into table) and load into sql server table in encry
pt
> form and then decrypt again when i create comma seperated file from table
> again to provide feed to another vendor ?
> Let me know, do u have similar thing ?
> "Shariq" wrote:
>|||Hello Shariq,
I'm Interesting about the encryption/decryption code that you wrote in VB.NE
T.
Would you like to post it me?
Thank you!
"Shariq" wrote:
> If your entire input and output processing is done with SQL server then I
> haven't done similar thing but you could possibly use ActiveX script to
> Encrypt/Decrypt.
> In a similiar type of situation when I had to encrypt/decrypt for input an
d
> output; I use VB.Net application to re-process XML file as input, encrypte
d
> the Credit Card numbers and output to an XML file. Then I executed a DTS
> package to upload the XML formatted output file to a SQL Server.
> When sending data to a client with decrypted CC numbers; I also used the V
B
> application to rad data from the SQL Server and during the data read,
> decrypted the CC numbers and produced XML file.
> "mvp" wrote:
>|||If you are taking about decrypting data coming in from the source data or
encrypting data as it extract to another format, then this would be
implemented at the application level not the database level. It depends on
what application programming tool your are using.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:8470213F-47A3-417A-B920-BB7759AC0738@.microsoft.com...
> Thanks for the reply
> But can i know how can i do this encryption-decryption..
> thanks
> "JT" wrote:
>|||x-rays,
You might be able to better technique but the technique I use works great
for me.
This function will either encrypt or decrypt depanding on what is passed to
it.
Public Function MyCryption(ByVal strInput As String) As String
' Encrypts/decrypts the passed string using
' a simple ASCII value-swapping algorithm
Dim strTempChar As String, i As Integer
For i = 1 To Len(strInput)
If Asc(Mid$(strInput, i, 1)) < 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) + 128, String)
ElseIf Asc(Mid$(strInput, i, 1)) > 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) - 128, String)
End If
Mid$(strInput, i, 1) = Chr(CType(strTempChar, Integer))
Next i
Return strInput
End Function
"x-rays" wrote:
> Hello Shariq,
> I'm Interesting about the encryption/decryption code that you wrote in VB.
NET.
> Would you like to post it me?
> Thank you!
> "Shariq" wrote:
>|||Keep in mind that whatever encryption you end up using, the encrypted data
is only as secure as the code you use for the encryption. If your
encryption code and key are stored in a source control system, everyone with
access to that system will be able to decrypt the data, provided they have
access to the table where the data is stored.
If your programmers do not have access to the production database, and your
DBAs do not have access to your application code, then you should be ok.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx

Friday, March 9, 2012

Encrypt and save in a file

Hi,
I want to save my business objects in an encrypted XML file. WHat is the
best way to do it? Examples and links are welcome.
Thank you,
SashaWrong group. I am sorry!
"Sasha" <noemail@.noemail.com> wrote in message
news:ub6fKd%23CEHA.3804@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to save my business objects in an encrypted XML file. WHat is the
> best way to do it? Examples and links are welcome.
> Thank you,
> Sasha
>

Wednesday, March 7, 2012

encoding iso-8859-1

Hi Folks
to accomodate an xml file that's encoded iso-8859-1, what do I need to
change in my .xsd code (below):
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
it looks like XMLSchema.xsd is 'wired' as utf-8. (I tried copying
XMLSchema.xsd, XMLSchema.DTD, datatypes.DTD locally and changing the encoding
in XMLSchema.xsd to iso-8859-1, but this causes other problems with simple
data type validations.)
thanks Rob
Did you add the XML declaration:
<?xml version="1.0" encoding="iso-8859-1"?>
in the beginning?
Best regards
Michael
"RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
news:1EF8BE76-F750-47D4-BA3C-6F785744C5B5@.microsoft.com...
> Hi Folks
> to accomodate an xml file that's encoded iso-8859-1, what do I need to
> change in my .xsd code (below):
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> --
> it looks like XMLSchema.xsd is 'wired' as utf-8. (I tried copying
> XMLSchema.xsd, XMLSchema.DTD, datatypes.DTD locally and changing the
> encoding
> in XMLSchema.xsd to iso-8859-1, but this causes other problems with simple
> data type validations.)
> thanks Rob
>
|||Hi Michael
I added this to the first line (in both the .xsd AND the .XML file). The
..XML file validates just fine, however, the .xsd (which I've copied locally,
with the DTDs) comes up with:
C:\Inetpub\wwwroot\XMLSchema.xsd(1359): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1359, 4).
C:\Inetpub\wwwroot\XMLSchema.xsd(1381): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1381, 4).
C:\Inetpub\wwwroot\XMLSchema.xsd(1400): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1400, 4).
C:\Inetpub\wwwroot\XMLSchema.xsd(1424): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1424, 4).
(The SimpleType definitions fail to validate.)
For the time being, there are only several instances in the XML files where
these characters appear (I think this is more of an error with the source,
than design), so I can remove them.
But eventually, I'd like to find a solution to this.
Thanks Rob
"Michael Rys [MSFT]" wrote:

> Did you add the XML declaration:
> <?xml version="1.0" encoding="iso-8859-1"?>
> in the beginning?
> Best regards
> Michael
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:1EF8BE76-F750-47D4-BA3C-6F785744C5B5@.microsoft.com...
>
>
|||I'm sorry, I failed to mention these facts:
1. The validation issues come up in VS.NET 2003
2. I can browse the XMLSchema (http://localhost/XMLSchema.xsd) w/o issue
(with only inserting <?xml version="1.0" encoding="iso-8859-1"?> into the .vsd
Thanks Rob
"Michael Rys [MSFT]" wrote:

> Did you add the XML declaration:
> <?xml version="1.0" encoding="iso-8859-1"?>
> in the beginning?
> Best regards
> Michael
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:1EF8BE76-F750-47D4-BA3C-6F785744C5B5@.microsoft.com...
>
>
|||The error message "Restriction of anySimpleType is not
allowed" indicates that the schema contains an error. You should fix the
schema (and use the general xml newsgroup or post the offending type
restriction for more help).
HTH
Michael
"RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
news:50F3E074-06A5-4DE8-A56E-36345FD40E0F@.microsoft.com...[vbcol=seagreen]
> Hi Michael
> I added this to the first line (in both the .xsd AND the .XML file). The
> .XML file validates just fine, however, the .xsd (which I've copied
> locally,
> with the DTDs) comes up with:
> C:\Inetpub\wwwroot\XMLSchema.xsd(1359): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1359, 4).
> C:\Inetpub\wwwroot\XMLSchema.xsd(1381): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1381, 4).
> C:\Inetpub\wwwroot\XMLSchema.xsd(1400): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1400, 4).
>
> C:\Inetpub\wwwroot\XMLSchema.xsd(1424): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1424, 4).
> (The SimpleType definitions fail to validate.)
> For the time being, there are only several instances in the XML files
> where
> these characters appear (I think this is more of an error with the source,
> than design), so I can remove them.
> But eventually, I'd like to find a solution to this.
> Thanks Rob
> "Michael Rys [MSFT]" wrote:

Friday, February 17, 2012

Empty XML to SSIS import

I am trying to take the national weather service XML snaphot and import into a SQL 2005 DB

http://www.weather.gov/data/current_obs/KHOT.xml

in theory this looks like it should work, but I get no data every time it runs

here is a sample of myXML source

<?xml version="1.0" encoding="ISO-8859-1"?>

<current_observation version="1.0"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="http://www.weather.gov/data/current_obs/current_observation.xsd">

<credit>NOAA's National Weather Service</credit>

<credit_URL>http://weather.gov/</credit_URL>

<image>

<url>http://weather.gov/images/xml_logo.gif</url>

<title>NOAA's National Weather Service</title>

</image>

<suggested_pickup>15 minutes after the hour</suggested_pickup>

<suggested_pickup_period>60</suggested_pickup_period>

<location>Hot Springs, Memorial Field Airport, AR</location>

<station_id>KHOT</station_id>

<observation_time>Last Updated on Sep 4, 12:53 pm CDT</observation_time>

<observation_time_rfc822>Tue, 4 Sep 2007 12:53:00 -0500 CDT</observation_time_rfc822>

<weather>Overcast</weather>

<temperature_string>81 F (27 C)</temperature_string>

<temp_f>81</temp_f>

<temp_c>27</temp_c>

<relative_humidity>74</relative_humidity>

<wind_string>From the South at 5 MPH</wind_string>

<wind_dir>South</wind_dir>

<wind_degrees>180</wind_degrees>

<wind_mph>4.6</wind_mph>

<wind_gust_mph>NA</wind_gust_mph>

<pressure_string>30.04" (1016.6 mb)</pressure_string>

<pressure_mb>1016.6</pressure_mb>

<pressure_in>30.04</pressure_in>

<dewpoint_string>72 F (22 C)</dewpoint_string>

<dewpoint_f>72</dewpoint_f>

<dewpoint_c>22</dewpoint_c>

<heat_index_string>85 F (29 C)</heat_index_string>

<heat_index_f>85</heat_index_f>

<heat_index_c>29</heat_index_c>

<windchill_string>NA</windchill_string>

<windchill_f>NA</windchill_f>

<windchill_c>NA</windchill_c>

<visibility_mi>10.00</visibility_mi>

<icon_url_base>http://weather.gov/weather/images/fcicons/</icon_url_base>

<icon_url_name>ovc.jpg</icon_url_name>

<two_day_history_url>http://www.weather.gov/data/obhistory/KHOT.html</two_day_history_url>

<ob_url>http://www.nws.noaa.gov/data/METAR/KHOT.1.txt</ob_url>

<disclaimer_url>http://weather.gov/disclaimer.html</disclaimer_url>

<copyright_url>http://weather.gov/disclaimer.html</copyright_url>

<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>

</current_observation>

The auto generated XSD file only gives me the subtable and no data

byt using XSD.exe I generated this file and it gives me the right structure. gives me two outputs from the xml source I need the one called current_observations)

but when I run this I get no data. When I look at the previews I get no data. I has to be something goofy in the XSD file but I dont know what it is.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="current_observation">
<xs:complexType>
<xs:sequence>
<xs:element name="credit" type="xs:string" minOccurs="0" msdata:Ordinal="0" />
<xs:element name="credit_URL" type="xs:string" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="suggested_pickup" type="xs:string" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="suggested_pickup_period" type="xs:string" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="location" type="xs:string" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="station_id" type="xs:string" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="latitude" type="xs:string" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="longitude" type="xs:string" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="observation_time" type="xs:string" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="observation_time_rfc822" type="xs:string" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="weather" type="xs:string" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="temperature_string" type="xs:string" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="temp_f" type="xs:string" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="temp_c" type="xs:string" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="relative_humidity" type="xs:string" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="wind_string" type="xs:string" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="wind_dir" type="xs:string" minOccurs="0" msdata:Ordinal="17" />
<xs:element name="wind_degrees" type="xs:string" minOccurs="0" msdata:Ordinal="18" />
<xs:element name="wind_mph" type="xs:string" minOccurs="0" msdata:Ordinal="19" />
<xs:element name="wind_gust_mph" type="xs:string" minOccurs="0" msdata:Ordinal="20" />
<xs:element name="pressure_string" type="xs:string" minOccurs="0" msdata:Ordinal="21" />
<xs:element name="pressure_mb" type="xs:string" minOccurs="0" msdata:Ordinal="22" />
<xs:element name="pressure_in" type="xs:string" minOccurs="0" msdata:Ordinal="23" />
<xs:element name="dewpoint_string" type="xs:string" minOccurs="0" msdata:Ordinal="24" />
<xs:element name="dewpoint_f" type="xs:string" minOccurs="0" msdata:Ordinal="25" />
<xs:element name="dewpoint_c" type="xs:string" minOccurs="0" msdata:Ordinal="26" />
<xs:element name="heat_index_string" type="xs:string" minOccurs="0" msdata:Ordinal="27" />
<xs:element name="heat_index_f" type="xs:string" minOccurs="0" msdata:Ordinal="28" />
<xs:element name="heat_index_c" type="xs:string" minOccurs="0" msdata:Ordinal="29" />
<xs:element name="windchill_string" type="xs:string" minOccurs="0" msdata:Ordinal="30" />
<xs:element name="windchill_f" type="xs:string" minOccurs="0" msdata:Ordinal="31" />
<xs:element name="windchill_c" type="xs:string" minOccurs="0" msdata:Ordinal="32" />
<xs:element name="visibility_mi" type="xs:string" minOccurs="0" msdata:Ordinal="33" />
<xs:element name="icon_url_base" type="xs:string" minOccurs="0" msdata:Ordinal="34" />
<xs:element name="icon_url_name" type="xs:string" minOccurs="0" msdata:Ordinal="35" />
<xs:element name="two_day_history_url" type="xs:string" minOccurs="0" msdata:Ordinal="36" />
<xs:element name="ob_url" type="xs:string" minOccurs="0" msdata:Ordinal="37" />
<xs:element name="disclaimer_url" type="xs:string" minOccurs="0" msdata:Ordinal="38" />
<xs:element name="copyright_url" type="xs:string" minOccurs="0" msdata:Ordinal="39" />
<xs:element name="privacy_policy_url" type="xs:string" minOccurs="0" msdata:Ordinal="40" />
<xs:element name="image" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="url" type="xs:string" minOccurs="0" />
<xs:element name="title" type="xs:string" minOccurs="0" />
<xs:element name="link" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="version" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="current_observation" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

I have a feeling im going to feel retarded once i find the answer...

can anyone tell me what ive screwed up?

|||I guess I feel better knowing that no one else who has seen this over the last few days has any idea either.|||Have you tried using a source script component?|||

no, i dont know what is wrong with it and therefore i dont know what I would script

i just looked and didnt see a script input under data flow.

this is what i am using in SSIS

http://www.vetteprojects.com/cthulhu/misc/work/ssis.pdf

|||anyone ?|||

The published xsd file from the NWS for this file is

<?xml version="1.0"?>
<xsdchema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:complexType name="imageType">
<xsdequence>
<xsd:element name="url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="title" type="xsd:token" minOccurs="0"/>
<xsd:element name="link" type="xsd:anyURI" minOccurs="0"/>
</xsdequence>
</xsd:complexType>
<xsd:element name="current_observation">
<xsd:complexType>
<xsdequence>
<xsd:element name="credit" type="xsd:token" minOccurs="0"/>
<xsd:element name="credit_URL" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="image" type="imageType" minOccurs="0"/>
<xsd:element name="suggested_pickup" type="xsd:token" minOccurs="0"/>
<xsd:element name="suggested_pickup_period" type="xsd:integer" minOccurs="0"/>
<xsd:element name="location" type="xsd:token" minOccurs="0"/>
<xsd:element name="station_id" type="xsd:token" minOccurs="0"/>
<xsd:element name="latitude" type="xsdtring" minOccurs="0"/>
<xsd:element name="longitude" type="xsdtring" minOccurs="0"/>
<xsd:element name="elevation" type="xsd:integer" minOccurs="0"/>
<xsd:element name="observation_time" type="xsd:token" minOccurs="0"/>
<xsd:element name="observation_time_rfc822" type="xsd:token" minOccurs="0"/>
<xsd:element name="weather" type="xsd:token" minOccurs="0"/>
<xsd:element name="temperature_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="temp_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="temp_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="relative_humidity" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="wind_dir" type="xsdtring" minOccurs="0"/>
<xsd:element name="wind_degrees" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_mph" type="xsdecimal" minOccurs="0"/>
<xsd:element name="wind_gust_mph" type="xsd:integer" minOccurs="0"/>
<xsd:element name="pressure_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="pressure_mb" type="xsdecimal" minOccurs="0"/>
<xsd:element name="pressure_in" type="xsdecimal" minOccurs="0"/>
<xsd:element name="dewpoint_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="dewpoint_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="dewpoint_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="heat_index_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="windchill_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="visibility_mi" type="xsdecimal" minOccurs="0"/>
<xsd:element name="icon_url_base" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="two_day_history_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_url_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="ob_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="disclaimer_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="copyright_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="privacy_policy_url" type="xsd:anyURI" minOccurs="0"/>
</xsdequence>
<xsd:attribute name="version" type="xsdtring" default="1.0"/>
</xsd:complexType>
</xsd:element>
</xsdchema>

is also fails to give me any output columns for "current_observation" and only give me the output for image_type

and when I just leave it with the image table and try to dump this into a file it is empty as well.

so im almost thiking the generated schmea that I am using above is not the problem.

does XML have to be validated before it can be imported into SSIS?

|||

I read here that the XML Source cannot handle XML where the root node has attributes or elements of simple types. To consume this XML, you'll have to change it to have a bogus root element. I just did this with your schema:

Code Snippet

<xsd:element name="bogus_root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="current_observation">
<xsd:complexType>
<xsd:sequence>

...

This worked and produced two outputs: current_observation and image. The XML Source added a current_observation_id column to both outputs to associate the two.|||

I made this change..

and added the appropriate closing parameters

and it does indeed now recognize the current observation side of it.. but it still isnt pulling any data.

when I goto preview the data it still shows no records

When I run the package it writes 0 rows

Code Snippet

<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:complexType'>http://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema">
<xsd:complexType name="imageType">
<xsd:sequence>
<xsd:element name="url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="title" type="xsd:token" minOccurs="0"/>
<xsd:element name="link" type="xsd:anyURI" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="bogus_root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="current_observation">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="credit" type="xsd:token" minOccurs="0"/>
<xsd:element name="credit_URL" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="image" type="imageType" minOccurs="0"/>
<xsd:element name="suggested_pickup" type="xsd:token" minOccurs="0"/>
<xsd:element name="suggested_pickup_period" type="xsd:integer" minOccurs="0"/>
<xsd:element name="location" type="xsd:token" minOccurs="0"/>
<xsd:element name="station_id" type="xsd:token" minOccurs="0"/>
<xsd:element name="latitude" type="xsd:string" minOccurs="0"/>
<xsd:element name="longitude" type="xsd:string" minOccurs="0"/>
<xsd:element name="elevation" type="xsd:integer" minOccurs="0"/>
<xsd:element name="observation_time" type="xsd:token" minOccurs="0"/>
<xsd:element name="observation_time_rfc822" type="xsd:token" minOccurs="0"/>
<xsd:element name="weather" type="xsd:token" minOccurs="0"/>
<xsd:element name="temperature_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="temp_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="temp_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="relative_humidity" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="wind_dir" type="xsd:string" minOccurs="0"/>
<xsd:element name="wind_degrees" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_mph" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="wind_gust_mph" type="xsd:integer" minOccurs="0"/>
<xsd:element name="pressure_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="pressure_mb" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="pressure_in" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="dewpoint_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="dewpoint_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="dewpoint_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="heat_index_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="windchill_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="visibility_mi" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="icon_url_base" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="two_day_history_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_url_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="ob_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="disclaimer_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="copyright_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="privacy_policy_url" type="xsd:anyURI" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="version" type="xsd:string" default="1.0"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

|||Did you change your input XML to match the changed schema?|||

I was afraid you were going to say that..

I am trying to grab this file every hour and import it. making a change to the xml everytime is not practical.

did you see the first XSD I posted? it is at the same stage right now as the provided xsd after your mods can it be modified to allow import without xml file mod ?

i will try this in the morning and let you know

thanks

|||

No, you will have to change the XML to match the schema change I made.

This would be simple to do in an XSL Transform, called in an XML Task before you process the data with the data flow.

|||

I tried to mod the xml file and still didnt get a record out of the file.

maybe I modded the file wrong

Code Snippet

<?xml version="1.0" encoding="ISO-8859-1"?>

<bogus_root>

<current_observation version="1.0"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>
</bogus_root>

Empty XML to SSIS import

I am trying to take the national weather service XML snaphot and import into a SQL 2005 DB

http://www.weather.gov/data/current_obs/KHOT.xml

in theory this looks like it should work, but I get no data every time it runs

here is a sample of myXML source

<?xml version="1.0" encoding="ISO-8859-1"?>

<current_observation version="1.0"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="http://www.weather.gov/data/current_obs/current_observation.xsd">

<credit>NOAA's National Weather Service</credit>

<credit_URL>http://weather.gov/</credit_URL>

<image>

<url>http://weather.gov/images/xml_logo.gif</url>

<title>NOAA's National Weather Service</title>

</image>

<suggested_pickup>15 minutes after the hour</suggested_pickup>

<suggested_pickup_period>60</suggested_pickup_period>

<location>Hot Springs, Memorial Field Airport, AR</location>

<station_id>KHOT</station_id>

<observation_time>Last Updated on Sep 4, 12:53 pm CDT</observation_time>

<observation_time_rfc822>Tue, 4 Sep 2007 12:53:00 -0500 CDT</observation_time_rfc822>

<weather>Overcast</weather>

<temperature_string>81 F (27 C)</temperature_string>

<temp_f>81</temp_f>

<temp_c>27</temp_c>

<relative_humidity>74</relative_humidity>

<wind_string>From the South at 5 MPH</wind_string>

<wind_dir>South</wind_dir>

<wind_degrees>180</wind_degrees>

<wind_mph>4.6</wind_mph>

<wind_gust_mph>NA</wind_gust_mph>

<pressure_string>30.04" (1016.6 mb)</pressure_string>

<pressure_mb>1016.6</pressure_mb>

<pressure_in>30.04</pressure_in>

<dewpoint_string>72 F (22 C)</dewpoint_string>

<dewpoint_f>72</dewpoint_f>

<dewpoint_c>22</dewpoint_c>

<heat_index_string>85 F (29 C)</heat_index_string>

<heat_index_f>85</heat_index_f>

<heat_index_c>29</heat_index_c>

<windchill_string>NA</windchill_string>

<windchill_f>NA</windchill_f>

<windchill_c>NA</windchill_c>

<visibility_mi>10.00</visibility_mi>

<icon_url_base>http://weather.gov/weather/images/fcicons/</icon_url_base>

<icon_url_name>ovc.jpg</icon_url_name>

<two_day_history_url>http://www.weather.gov/data/obhistory/KHOT.html</two_day_history_url>

<ob_url>http://www.nws.noaa.gov/data/METAR/KHOT.1.txt</ob_url>

<disclaimer_url>http://weather.gov/disclaimer.html</disclaimer_url>

<copyright_url>http://weather.gov/disclaimer.html</copyright_url>

<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>

</current_observation>

The auto generated XSD file only gives me the subtable and no data

byt using XSD.exe I generated this file and it gives me the right structure. gives me two outputs from the xml source I need the one called current_observations)

but when I run this I get no data. When I look at the previews I get no data. I has to be something goofy in the XSD file but I dont know what it is.

I have a feeling im going to feel retarded once i find the answer...

can anyone tell me what ive screwed up?

|||I guess I feel better knowing that no one else who has seen this over the last few days has any idea either.|||Have you tried using a source script component?|||

no, i dont know what is wrong with it and therefore i dont know what I would script

i just looked and didnt see a script input under data flow.

this is what i am using in SSIS

http://www.vetteprojects.com/cthulhu/misc/work/ssis.pdf

|||anyone ?|||

The published xsd file from the NWS for this file is

is also fails to give me any output columns for "current_observation" and only give me the output for image_type

and when I just leave it with the image table and try to dump this into a file it is empty as well.

so im almost thiking the generated schmea that I am using above is not the problem.

does XML have to be validated before it can be imported into SSIS?

|||

Code Snippet

<xsd:element name="bogus_root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="current_observation">
<xsd:complexType>
<xsd:sequence>

...

This worked and produced two outputs: current_observation and image. The XML Source added a current_observation_id column to both outputs to associate the two.|||

I made this change..

and added the appropriate closing parameters

and it does indeed now recognize the current observation side of it.. but it still isnt pulling any data.

when I goto preview the data it still shows no records

When I run the package it writes 0 rows

Code Snippet

|||Did you change your input XML to match the changed schema?|||

I was afraid you were going to say that..

I am trying to grab this file every hour and import it. making a change to the xml everytime is not practical.

did you see the first XSD I posted? it is at the same stage right now as the provided xsd after your mods can it be modified to allow import without xml file mod ?

i will try this in the morning and let you know

thanks

|||

No, you will have to change the XML to match the schema change I made.

This would be simple to do in an XSL Transform, called in an XML Task before you process the data with the data flow.

|||

I tried to mod the xml file and still didnt get a record out of the file.

maybe I modded the file wrong

Code Snippet

<?xml version="1.0" encoding="ISO-8859-1"?>

<bogus_root>

<current_observation version="1.0"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>
</bogus_root>

Empty XML to SSIS import

I am trying to take the national weather service XML snaphot and import into a SQL 2005 DB

http://www.weather.gov/data/current_obs/KHOT.xml

in theory this looks like it should work, but I get no data every time it runs

here is a sample of myXML source

<?xml version="1.0" encoding="ISO-8859-1"?>

<current_observation version="1.0"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="http://www.weather.gov/data/current_obs/current_observation.xsd">

<credit>NOAA's National Weather Service</credit>

<credit_URL>http://weather.gov/</credit_URL>

<image>

<url>http://weather.gov/images/xml_logo.gif</url>

<title>NOAA's National Weather Service</title>

</image>

<suggested_pickup>15 minutes after the hour</suggested_pickup>

<suggested_pickup_period>60</suggested_pickup_period>

<location>Hot Springs, Memorial Field Airport, AR</location>

<station_id>KHOT</station_id>

<observation_time>Last Updated on Sep 4, 12:53 pm CDT</observation_time>

<observation_time_rfc822>Tue, 4 Sep 2007 12:53:00 -0500 CDT</observation_time_rfc822>

<weather>Overcast</weather>

<temperature_string>81 F (27 C)</temperature_string>

<temp_f>81</temp_f>

<temp_c>27</temp_c>

<relative_humidity>74</relative_humidity>

<wind_string>From the South at 5 MPH</wind_string>

<wind_dir>South</wind_dir>

<wind_degrees>180</wind_degrees>

<wind_mph>4.6</wind_mph>

<wind_gust_mph>NA</wind_gust_mph>

<pressure_string>30.04" (1016.6 mb)</pressure_string>

<pressure_mb>1016.6</pressure_mb>

<pressure_in>30.04</pressure_in>

<dewpoint_string>72 F (22 C)</dewpoint_string>

<dewpoint_f>72</dewpoint_f>

<dewpoint_c>22</dewpoint_c>

<heat_index_string>85 F (29 C)</heat_index_string>

<heat_index_f>85</heat_index_f>

<heat_index_c>29</heat_index_c>

<windchill_string>NA</windchill_string>

<windchill_f>NA</windchill_f>

<windchill_c>NA</windchill_c>

<visibility_mi>10.00</visibility_mi>

<icon_url_base>http://weather.gov/weather/images/fcicons/</icon_url_base>

<icon_url_name>ovc.jpg</icon_url_name>

<two_day_history_url>http://www.weather.gov/data/obhistory/KHOT.html</two_day_history_url>

<ob_url>http://www.nws.noaa.gov/data/METAR/KHOT.1.txt</ob_url>

<disclaimer_url>http://weather.gov/disclaimer.html</disclaimer_url>

<copyright_url>http://weather.gov/disclaimer.html</copyright_url>

<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>

</current_observation>

The auto generated XSD file only gives me the subtable and no data

byt using XSD.exe I generated this file and it gives me the right structure. gives me two outputs from the xml source I need the one called current_observations)

but when I run this I get no data. When I look at the previews I get no data. I has to be something goofy in the XSD file but I dont know what it is.

I have a feeling im going to feel retarded once i find the answer...

can anyone tell me what ive screwed up?

|||I guess I feel better knowing that no one else who has seen this over the last few days has any idea either.|||Have you tried using a source script component?|||

no, i dont know what is wrong with it and therefore i dont know what I would script

i just looked and didnt see a script input under data flow.

this is what i am using in SSIS

http://www.vetteprojects.com/cthulhu/misc/work/ssis.pdf

|||anyone ?|||

The published xsd file from the NWS for this file is

is also fails to give me any output columns for "current_observation" and only give me the output for image_type

and when I just leave it with the image table and try to dump this into a file it is empty as well.

so im almost thiking the generated schmea that I am using above is not the problem.

does XML have to be validated before it can be imported into SSIS?

|||

Code Snippet

<xsd:element name="bogus_root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="current_observation">
<xsd:complexType>
<xsd:sequence>

...

This worked and produced two outputs: current_observation and image. The XML Source added a current_observation_id column to both outputs to associate the two.|||

I made this change..

and added the appropriate closing parameters

and it does indeed now recognize the current observation side of it.. but it still isnt pulling any data.

when I goto preview the data it still shows no records

When I run the package it writes 0 rows

Code Snippet

|||Did you change your input XML to match the changed schema?|||

I was afraid you were going to say that..

I am trying to grab this file every hour and import it. making a change to the xml everytime is not practical.

did you see the first XSD I posted? it is at the same stage right now as the provided xsd after your mods can it be modified to allow import without xml file mod ?

i will try this in the morning and let you know

thanks

|||

No, you will have to change the XML to match the schema change I made.

This would be simple to do in an XSL Transform, called in an XML Task before you process the data with the data flow.

|||

I tried to mod the xml file and still didnt get a record out of the file.

maybe I modded the file wrong

Code Snippet

<?xml version="1.0" encoding="ISO-8859-1"?>

<bogus_root>

<current_observation version="1.0"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>
</bogus_root>

Tuesday, March 27, 2012

Encryption question

Friday, March 9, 2012

Encrypt and save in a file

Wednesday, March 7, 2012

encoding iso-8859-1

Friday, February 17, 2012

Empty XML to SSIS import

Empty XML to SSIS import

Empty XML to SSIS import

Empty Column

Blog Archive

About Me