Empty Column: import

Showing posts with label import. Show all posts

Monday, March 26, 2012

Encryption in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,
I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,
Could you please get me the name of the book.
Thank You
Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Encryption in SSIS Package

Hello,

JatinShah wrote:

Hello,
I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,
Could you please get me the name of the book.
Thank You
Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Thursday, March 22, 2012

Encryption - importing keys

Is it possible to load data into MSSQL 2005 that has been encrypted externally with a symmetric key algorithm, such as AES, and then import the key to SQL Server? After browsing through Books Online, I don't see any way to import a symmetric key from an external file, but maybe I'm missing something.

No, at this time, it is not possible to do that. You would have to decrypt the data and then encrypt it again using SQL Server's encryption functions.

Thanks
Laurentiu

|||

If your primary concern is protecting data in transit, you should be able to use SSL to accomplish this:

http://msdn2.microsoft.com/en-us/library/ms189067.aspx

Sung

Friday, March 9, 2012

Encrypt Data

Hi,

In SQL2000 i need to Encryptdata when I export data using DTS.LikeWise I should when i import data I should authenticate that user and decrypt that.

Can any one help?

Thanks,

Karthik

vgvKarthik wrote:

Hi,
In SQL2000 i need to Encryptdata when I export data using DTS.LikeWise I should when i import data I should authenticate that user and decrypt that.
Can any one help?
Thanks,
Karthik

Yeah. Try a different forum. This has got nothing to do with SSIS.

-Jamie

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>

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>

Monday, March 26, 2012

Encryption in SSIS Package

Encryption in SSIS Package

Thursday, March 22, 2012

Encryption - importing keys

Friday, March 9, 2012

Encrypt Data

Friday, February 17, 2012

Empty XML to SSIS import

Empty XML to SSIS import

Empty XML to SSIS import

Empty XML to SSIS import

Empty Column

Blog Archive

About Me