Problem in Import XML File From SSIS - Database Discussion Boards

Qazzy648-Oct-12 8:27

8-Oct-12 8:27

I want to get a count of an ID where the ID is either = -999 or its not. So i have 2 counts in total. I used union all to get that but i think there would be better way to get the count from the same field. any help would be appreciated.

select count(ID) as Id_999 from table_A where ID='-999'
union all
select count(ID) from table_A where ID<>'-999'

there is no null values allowed so it either a number or -999 if null when table is loaded.

Re: SQL counting

scottgp8-Oct-12 9:33

scottgp

8-Oct-12 9:33

You could turn the counts into separate columns:
select SUM (
CASE
WHEN ID = '-999' THEN 1
ELSE 0
END)
AS ID999_count,
SUM (
CASE
WHEN ID <> '-999' THEN 1
ELSE 0
END)
AS nonID999_count
from table_A

Scott

Re: SQL counting

Qazzy648-Oct-12 10:07

Qazzy64

8-Oct-12 10:07

Thanks Scott it works great. I was close to that but i was putting ID, then the sum(CASE statement). Thanks again.

Re: SQL counting

Eddy Vluggen8-Oct-12 13:25

Eddy Vluggen

8-Oct-12 13:25

Qazzy64 wrote:
there is no null values allowed so it either a number or -999 if null when table is loaded.

Just curious; does that mean that you replace a null-test with a test for -999, and act according? Why was null disallowed anyway?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL counting

Qazzy648-Oct-12 17:35

Qazzy64

8-Oct-12 17:35

When they load the table it has a number(ID) or if its null they load -999 so they know to reprocess it later on when other matching data shows up. Hope that helps.

Re: SQL counting

Eddy Vluggen9-Oct-12 5:08

Eddy Vluggen

9-Oct-12 5:08

The reason I asked is because it does not make any sense to me; you'd still have to deal with the same type of checks. The only difference being that the new "strategy" will be alien to new developers, and will cause weirder and harder to debug-exceptions than a null-reference would.

I might be missing some obvious advantage of the approach. So, where is it? What's the 'added value' of using a marker-value above a null-value?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL counting

Qazzy649-Oct-12 7:16

Qazzy64

9-Oct-12 7:16

I agree with you that doing anything causes more problems eventually. Myself i would not have brought the data forward until all fields are present from source file. But i do not make that choice and i got involved after project was started. This is more of a transactional system then data warehouse like they said. Hate to say it but it goes back to the way it was architected and going forward from there has been to get it to work. Very complex data modeling which i would have simplified by bringing it in and leveling data types. They chose to bring it forward anyway and then leave just before the DW level. Makes no sense but i wasnt in on those meetings. I get to deal with it and figure out if the data is accurate. Fun times. Smile | :)

Problem in Import XML File From SSIS

mos_tafino@hotmail.com8-Oct-12 1:31

mos_tafino@hotmail.com

8-Oct-12 1:31

Hi Mr
i have a problem in ssis when i tried to use xml file to import to any data sources

i used Multicast to test no or rows returned but no returned rows
i can't attach xml file in the message

please save as GetGroups.xml and test it and send me the result

XML

<?xml version="1.0" encoding="utf-16"?>
<DataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="T">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ID" type="xs:int" minOccurs="0" />
                <xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
                <xs:element name="NodeCount" type="xs:int" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet>
      <T diffgr:id="T1" msdata:rowOrder="0">
        <ID>648</ID>
        <NodeGroup>Moustord</NodeGroup>
        <NodeCount>5</NodeCount>
      </T>
      <T diffgr:id="T2" msdata:rowOrder="1">
        <ID>646</ID>
        <NodeGroup>Shell</NodeGroup>
        <NodeCount>9</NodeCount>
      </T>
      <T diffgr:id="T3" msdata:rowOrder="2">
        <ID>647</ID>
        <NodeGroup>Suze</NodeGroup>
        <NodeCount>4</NodeCount>
      </T>
    </NewDataSet>
  </diffgr:diffgram>
</DataSet>

modified 9-Oct-12 1:55am.

Re: Problem in Import XML File From SSIS

mos_tafino@hotmail.com8-Oct-12 19:52

mos_tafino@hotmail.com

8-Oct-12 19:52

i tried to use OPENROWSET like that but it's not work can you modify it

DECLARE @xml XML

SET @xml = '<DataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="T">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ID" type="xs:int" minOccurs="0" />
                <xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
                <xs:element name="NodeCount" type="xs:int" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet>
      <T diffgr:id="T1" msdata:rowOrder="0">
        <ID>648</ID>
        <NodeGroup>Moustord</NodeGroup>
        <NodeCount>5</NodeCount>
      </T>
      <T diffgr:id="T2" msdata:rowOrder="1">
        <ID>646</ID>
        <NodeGroup>Shell</NodeGroup>
        <NodeCount>9</NodeCount>
      </T>
      <T diffgr:id="T3" msdata:rowOrder="2">
        <ID>647</ID>
        <NodeGroup>Suze</NodeGroup>
        <NodeCount>4</NodeCount>
      </T>
    </NewDataSet>
  </diffgr:diffgram>
</DataSet>'

 
;WITH XMLNAMESPACES( 
        'http://www.w3.org/2001/XMLSchema' AS ns,
        'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr )
SELECT 
        fd.c.value('(ID/text())[1]', 'INT') 'a',
        fd.c.value('(NodeGroup/text())[1]', 'nvarchar(255)') 'b',
        fd.c.value('(NodeCount/text())[1]', 'INT') 'c'
        
FROM @xml.nodes('ns:DataSet/diffgr:diffgram/NewDataSet/T') fd(c)

Problem with MySQL stored procedure

Jassim Rahma7-Oct-12 7:27

Jassim Rahma

7-Oct-12 7:27

Hi,

What's the problem with this code?:

SQL

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
    DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

    IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    BEGIN
        UPDATE volow_members SET is_channel_created = FALSE;

        SET param_is_channel_created = FALSE;
    END;
    ELSE SET param_is_channel_created = TRUE;
    END IF;
END

I am not getting any error on mysql neither on the .net application?!!

here is my .net code:

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();

sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_volow_member_id", Convert.ToInt32(Session["volow_member_id"])).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;

// execute the query;
int result_row = sql_command.ExecuteNonQuery();

if (Convert.ToBoolean(sql_command.Parameters["param_is_channel_created"].Value) != true)
{ Session["is_channel_created"] = false; }

Re: Problem with MySQL stored procedure

Jörgen Andersson7-Oct-12 10:43

Jörgen Andersson

7-Oct-12 10:43

Not getting any errors could sometimes be thought of as a good thing.

What's the problem?

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Problem with MySQL stored procedure

Jassim Rahma7-Oct-12 10:52

Jassim Rahma

7-Oct-12 10:52

that's what's making me crazy!

no error in Mysql nor C# code

but the MySQL code is not getting invoke. DELETE is not happening!

Re: Problem with MySQL stored procedure

Eddy Vluggen8-Oct-12 0:51

Eddy Vluggen

8-Oct-12 0:51

..
Try 'debugging'. What does the row-count return? What happens if you put a 'SELECT 1' in there, without any parameters, does that execute?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Problem with MySQL stored procedure

Bernhard Hiller7-Oct-12 22:26

Bernhard Hiller

7-Oct-12 22:26

How do you know that the DELETE did not happen? Did you check in the database?
What do you expect to get at int result_row = sql_command.ExecuteNonQuery();?

Re: Problem with MySQL stored procedure

Jassim Rahma7-Oct-12 23:31

Jassim Rahma

7-Oct-12 23:31

yes i checked the database itself. records were not deleted and no error were thrown

Re: Problem with MySQL stored procedure

Shameel8-Oct-12 1:56

Shameel

8-Oct-12 1:56

This line

sql_command.Parameters.AddWithValue("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;

is wrong. AddWithValue expects a value as the second parameter but you are providing it a data type (did you mean to call Add method?). Anyway, you should not be providing a value to an output parameter.

I'm not sure if this is what is the cause of your problem.

Re: Problem with MySQL stored procedure

Jassim Rahma8-Oct-12 6:54

Jassim Rahma

8-Oct-12 6:54

how can i proivide an value?? it's an OUT parameter

Re: Problem with MySQL stored procedure

Jassim Rahma8-Oct-12 7:11

Jassim Rahma

8-Oct-12 7:11

I even tried this simple delete but still not working. No error and records not getting deleted!!!

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();
                                        
sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
int result_row = sql_command.ExecuteNonQuery();

and SQL:

SQL

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
    DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    DELETE FROM member_channels WHERE member_channel_id = param_channel_id;
 
    IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    BEGIN
        UPDATE volow_members SET is_channel_created = FALSE;
 
        SET param_is_channel_created = FALSE;
    END;
    ELSE SET param_is_channel_created = TRUE;
    END IF;
END

terminating SqlConnection.Open() call while connecting

Danzy834-Oct-12 7:43

Danzy83

4-Oct-12 7:43

Hi everyone. I'm trying open a connection to sql server database in a background thread while showing a progress dialogbox that has a Cancel button.

I would like to know how I can terminate the connection when the Cancel button is clicked and SqlConnection.Open call is still connecting. Thanks.

Re: terminating SqlConnection.Open() call while connecting

Shameel4-Oct-12 8:46

Shameel

4-Oct-12 8:46

I'm afraid you cannot do that. SqlConnection.Open is a blocking call and control will not return to the calling code until the method completes. ~~And I'm not aware of any asynchronous way of opening a database connection.~~

I stand corrected. Check Eddy's answer below for an asynchronous way of doing this. Smile | :)

modified 4-Oct-12 15:20pm.

Re: terminating SqlConnection.Open() call while connecting

Eddy Vluggen4-Oct-12 9:10

Eddy Vluggen

4-Oct-12 9:10

Use OpenAsync[^], or it's overload[^].

--edit
For which version of Sql Server? Take note of the "supported platforms" in the documentation.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: terminating SqlConnection.Open() call while connecting

Danzy835-Oct-12 4:37

Danzy83

5-Oct-12 4:37

Thanks Eddy. The function is available only in .net framework 4.5 which I'm not using.

I tried in my own way which works fine now. My BackgroundWorker thread creates a new thread and the created thread rather calls SqlConnection.Open(). When the cancel button is clicked, the BackgroundWorker thread aborts the thread opening the connection.

It works fine although I don't know if there could be any related problems. I have run the application several times just to try that part of the code and I haven't found any problems.

Re: terminating SqlConnection.Open() call while connecting

Eddy Vluggen5-Oct-12 4:57

Eddy Vluggen

5-Oct-12 4:57

Dan_K wrote:
I tried in my own way which works fine now. My BackgroundWorker thread creates a new thread and the created thread rather calls SqlConnection.Open(). When the cancel button is clicked, the BackgroundWorker thread aborts the thread opening the connection.

That's what the OpenAsync in 4.5 will do; open the query on a backgroundthread, polling it's status.

Dan_K wrote:
It works fine although I don't know if there could be any related problems.

Opening a connection can fail for multiple reasons, and exceptions aren't casted across the thread. You might want to make sure you have a good exception-handler in there; if you have the time, then it'd be mighty cool to have a "try connecting again" function - that way people won't have to relaunch the app after they found out that the database-server wasn't powered on.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

SQL 2000 to 2008R2 Query Performance

cjb1104-Oct-12 5:13

cjb110

4-Oct-12 5:13

We've upgraded one of our systems to SQL 2008R2 and certain parts are running like a dog. One statement that would have taken an average of 20-40mins on the old box, took from 11:40 to 00:35. Its not every statement but odd ones.

Its a 2 cpu box, and the cpu usage sits a 50%, there's hardly any activity in activity monitor (no significant resource waits etc, no io, or requests)

Unfortunatley this didn't happen during conversion, system test or uat, so we've imp'ed, and now hitting these issues Frown | :(

Has anybody got some hints, places to look, good resources to read up on?

One thing we would like to work out, is it a fact, 2008 does things differently and query design, indexes etc need to be re-developed/analysed?

Re: SQL 2000 to 2008R2 Query Performance

Shameel4-Oct-12 9:01

Shameel

4-Oct-12 9:01

Rebuild all indexes and do update statistics and then try again.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.