Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

sql

Float return

When the table below is created -the data selected
is different for values > 10. Does some one know why
float behaves this way ? - I am stumped
create table tempdb.dbo.TestValue (ColId int, TheValue
Float)
Insert into TestValue Values (1, 10.25)
Insert into TestValue Values (2, 10.99)
Insert into TestValue Values (3, 9.9)
Insert into TestValue Values (4, 6.59)
select * from TestValue
Results:
========
1 10.25
2 10.99
3 9.9000000000000004
4 6.5899999999999999PBrent
Read up "float and real" chapter in the BOL as well as visit on Aaron's web
site www.aspfaq.com to get more info and examples whu this datatype behaves
this way.
"PBrent" <PBrent@.discussions.microsoft.com> wrote in message
news:23d001c53f65$e8aff6a0$a401280a@.phx.gbl...
> When the table below is created -the data selected
> is different for values > 10. Does some one know why
> float behaves this way ? - I am stumped
> create table tempdb.dbo.TestValue (ColId int, TheValue
> Float)
> Insert into TestValue Values (1, 10.25)
> Insert into TestValue Values (2, 10.99)
> Insert into TestValue Values (3, 9.9)
> Insert into TestValue Values (4, 6.59)
> select * from TestValue
> Results:
> ========
> 1 10.25
> 2 10.99
> 3 9.9000000000000004
> 4 6.5899999999999999|||It's nothing special about 10. If you insert the values 16.9 into a float,
the actual floating-point value stored is just under 16.9, and you get this
Insert into TestValue Values (5, 16.9)
...
16.899999999999999
Of the numbers you inserted into the table, only 10.25 can be
stored exactly as a float. The others are stored as the nearest
representable floating-point value. When these approximations
are converted back to decimals for display, sometimes you see
the difference from the original number you tried to enter, and
sometimes you are lucky and they are rounded back to the
number you started with.
Steve Kass
Drew University
PBrent wrote:

>When the table below is created -the data selected
>is different for values > 10. Does some one know why
>float behaves this way ? - I am stumped
>create table tempdb.dbo.TestValue (ColId int, TheValue
>Float)
>Insert into TestValue Values (1, 10.25)
>Insert into TestValue Values (2, 10.99)
>Insert into TestValue Values (3, 9.9)
>Insert into TestValue Values (4, 6.59)
>select * from TestValue
>Results:
>========
>1 10.25
>2 10.99
>3 9.9000000000000004
>4 6.5899999999999999
>

Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approxim
ate datatype. Which means that the exact number stored is not always what w
as inteded for storage. There is no way to get around this unless you handl
e your own rounding (See Ro
und function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, lik
e
a NUMERIC datatype, for instance.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:

>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's syste
m
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as well

Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approximate datatype. Which means that the exact number stored is not always what was inteded for storage. There is no way to get around this unless you handle your own rounding (See Round function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, like
a NUMERIC datatype, for instance.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:
>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's system
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as wellsql

Float Datatype Truncation Bug

Hi Friends,

I have a table

Create table #table1(a float)

insert into #table1 values(123456789.987654321)

select a from #table1

drop table #table1

when i run this in SQLServer 2005 Management Studio i get the following truncated output

(1 row(s) affected)

a

-

123456789.987654

(1 row(s) affected)

when i run this query using SQL Query Analyser or OSQL Utility i get the following output

(1 row(s) affected)

a
--
123456789.98765431

(1 row(s) affected)

I want the full output in SQLServer 2005 itself.... Is this a microsoft bug?

I would like to know how to fix this?

Thanks and Regards,

It is not a bug. Your data is not truncated on the table(while storing). its bcs of the Management Console only.(MC result only truncate the values). Connect the same SQL Server 2005 from QA you will get the same result as 2000 (in your case your proper result).

If you want to trust your result use the following query.(explicit precision setting)

Code Snippet

Create table #table1(a float)

Insert into #table1 values(123456789.987654321)

Select cast(a as numeric(38,8)) from #table1

Drop table #table1

float datatype

I have several fields in a table with a float datatype. When the user
enters 1.1 into the database, it returns 1.1000000000000001 -- I would think
it should be 1.1000000000000000.
Won't this eventually cause mathematically errors and what do I do about
this.
FLOAT is an approximate numeric type. If you require accurate,
fixed-precision results from calculations then use an exact type such as
NUMERIC.
David Portas
SQL Server MVP

float datatype

I have several fields in a table with a float datatype. When the user
enters 1.1 into the database, it returns 1.1000000000000001 -- I would think
it should be 1.1000000000000000.
Won't this eventually cause mathematically errors and what do I do about
this.FLOAT is an approximate numeric type. If you require accurate,
fixed-precision results from calculations then use an exact type such as
NUMERIC.
David Portas
SQL Server MVP
--

Float Datatype

Hi Friends,

I have a table

Create table #table1(a float)

insert into #table1 values(123456789.987654321)

select a from #table1

drop table #table1

when i run this in SQLServer 2005 Management Studio i get the following truncated output

(1 row(s) affected)

a

-

123456789.987654

(1 row(s) affected)

when i run this query using SQL Query Analyser or OSQL Utility i get the following output

(1 row(s) affected)

a
--
123456789.98765431

(1 row(s) affected)

I want the full output in SQLServer 2005 itself.... Is this a microsoft bug?

I would like to know how to fix this?

Thanks and Regards,

It is not a bug. Your data is not truncated on the table(while storing). its bcs of the Management Console only.(MC result only truncate the values). Connect the same SQL Server 2005 from QA you will get the same result as 2000 (in your case your proper result).

If you want to trust your result use the following query.(explicit precision setting)

Code Snippet

Create table #table1(a float)

Insert into #table1 values(123456789.987654321)

Select cast(a as numeric(38,8)) from #table1

Drop table #table1

sql

float datatype

I have several fields in a table with a float datatype. When the user
enters 1.1 into the database, it returns 1.1000000000000001 -- I would think
it should be 1.1000000000000000.
Won't this eventually cause mathematically errors and what do I do about
this.FLOAT is an approximate numeric type. If you require accurate,
fixed-precision results from calculations then use an exact type such as
NUMERIC.
--
David Portas
SQL Server MVP
--

Flipping answers in a table.

Hello all,

I have a simple task that I need to switch all the answers in my tables from yes(11) to no(10) and no to yes. It is simple enough to just make three update statements that will do this but is there a more elegant way to do this swap from one update statment.

Here are the three update statments:
update ans_test
set ans_chc_id = 99
where ans_chc_id = 11

update ans_test
set ans_chc_id = 11
where ans_chc_id = 10

update ans_test
set ans_chc_id = 10
where ans_chc_id = 99

Use CASE statements to allow a single update statement to do the work. Something like:

Code Snippet

update ans_test
set ans_chc_id
= case when ans_chc_id = 11 then 10
else 11
end
--where ans_chc_id between 10 and 11

The WHERE statement is necessary ONLY if the column contains values other than 10 and 11 and you do not want the row update in that particular case.

|||Excellent, thank you very much for your help.
|||

This 'seems' somewhat 'odd'. Normally a Yes/No question 'should' have three responses: Yes/No/Unknown. And equally often the data is stored as 0/1/NULL. I don't get values like 10, 11, and 99. It seems like you are really making things unnecessarily difficult for youself.

However, expanding on Kent's suggestion, you can handle the flipping 'flipping' with a CASE structure similar to this:

Code Snippet

UPDATE Ans_Test
SET Ans_Chc_ID = CASE
WHEN 10 THEN 99
WHEN 11 THEN 10
WHEN 99 THEN 11
END
WHERE {criteria}

Flawed SQL Procedure

I am using the below procedure to set the field "Completed" to "True" in the table "Orders" only when the customer have paid and received or downloaded all his produts.

~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREUserName = @.UserName

ANDCompleted = 0

~~~~~~~~~~~~~~~~~~~~~~~~~

Which is obviously flawed because I predict a situation where thesame customer ( user1 ) could havetwo different orders, like in the below example, when this procedurewill set incorrectly both fields "Completed" to "True" ( in tableOrders ) forOrderID = 1 and OrderID = 2 when actually thenot-downloadable product "gadget105" wasnot received yet by the customer (Received=False in table "OrderDetails" ).

Observations:

1)Downloadable products likesoftware have their field "Received" set toNULL because theydo not need to be shipped and therefore completing this field is irrelevant.

2) Both orders (OrderID = 1 and 2 ) were made by thesame customer withUserName = "user1".

3) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer.

Table OrderDetails

_______________________________________________________________

OrderID ProductID ProductName Downloadable Quantity Received UnitCost

1 10 software10 True 1 NULL 15.00

1 101 gadget101 False 1 True 20.00

2 12 software12 True 1 NULL 16.00

2 105 gadget105 False 1 False 22.00

2 13 software13 True 1 NULL 22.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table Orders

_______________________________________________________________

OrderID UserName PaymentConfirmed Completed

1 user1 True False

2 user1 True False

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to solve the problem ?

You woul dprobably need to use the OrderId also in the WHERE clause.. so only the specific orders get "completed"|||

Hi ndinakar

But that is the problem, the procedure itself has to be capable to find out which OrderIDs must set the field Completed to True in the table .

|||

I can obtain the information that all downloadable items were downloaded by the client by verifing the field "RemoveRole" in theCustomerDownload table ( not shown here ) set to 'yes'.

Based on that, I devised this new procedure but since I am not good with "INNER JOINs", can somebody tell me if it is correct ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREOrderID = (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In this procedure I aim to set the field Completed to True for all the customer's orders that satisfy the above conditions.

|||

If your subquery returs multiple records your query could fail. Perhaps you might want to do an IN instead of "=".

UPDATEOrders

SETCompleted = 1

WHEREOrderID IN (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

Flattening Parent Child, an issue, please help

Hello Experts,
Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?
Please help.
Thanks.
SoumyaDip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
How do modify the code to work for any level rather than hard coding
the level up to "5"?

If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.

If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,
I was wondering whether anyone has any sample "Dynamic SQL Code" that I
can use to resolve this issues.
Thanks for any help.
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?

>
If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.
>
If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||>Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.|||Hi Celko,
Thanks for your input.
The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!
Any help is greatly appriciated.
Thanks.
Soumya

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. <<

>
I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding the level up to "5"? <<

>
One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.
>
A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.
>
The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.
>
You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

>
You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

Did you even look at the article I posted the link to?

What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.

It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)

I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.

What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.

I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.

I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

>
Did you even look at the article I posted the link to?
>
What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.
>
It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)
>
I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.
>
What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Flattened results: How to get column names?

I read on this forum that you can get flattened results using this code:

table = New DataTable()

Using dataAdapter As AdomdDataAdapter = New AdomdDataAdapter(command)

dataAdapter.Fill(table)

End Using

When you do this, the first few columns in the datatable are actually row header values. That's exactly what I want. Correspondingly I expect the first few rows to be the column header values. But they are not. Why not? Is there any easy way to get the column header values?

I think you will find that the ColumnName property of the column contains some sort of concatenation of the column headers.

|||The column name is something like [abc].[def].&[ghi].[jkl].&[mno]. I guess I could parse this myself. So parsing this is the only way to get the column headers? That seems unreliable. For row headers I can easily find them in the body of the table, and I don't have to do any parsing.

|||

This is just how the flattened rowsets work. If you have ever used a linked server in SQL Server back to an SSAS server you will have seen similar result sets. The only other option would be to get a cellset (which is multi-dimensional) and flatten it yourself.

sql

flatten out a normalized child table?

I need to extract Customer Order data, and join it to normalized ship-to
table so I can get their address on a single line/row of data. The Column
names are not importat in that final flat file, just what was in
row1,2,3,...6
GARY C Test Row1
LISA C Test Row2
816 RIVERVIEW PLACE Row3
WASHINGTON, MO 63090 Row4
Row5
Row6
THOMAS H Other-Test Row1
2102 N SHAMROCK RD Row2
BEL AIR, MD 21014 Row3
Row4 ,etc.
I have to account for double names names, and possibably titles, Suite #,
etc.
TIAPlease post DDL with your sample data. What is the key of the table you
posted? What relates the address lines together to make a single
address? Apparently nothing links an address together in the sketch you
posted except for the order in which you typed them out. We know that
tables have no fixed order so it isn't possible to combine the rows
reliably to make addresses out of each one.
If you had an additional column such as contact_name or contact_no for
each address line then you could do something like:
SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
MAX(CASE WHEN row_num = 2 THEN addr END),
MAX(CASE WHEN row_num = 3 THEN addr END),
... etc
FROM your_table
GROUP BY contact_name ;
Hope this helps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129058354.597302.39690@.g44g2000cwa.googlegroups.com...
> Please post DDL with your sample data. What is the key of the table you
> posted? What relates the address lines together to make a single
> address? Apparently nothing links an address together in the sketch you
> posted except for the order in which you typed them out. We know that
> tables have no fixed order so it isn't possible to combine the rows
> reliably to make addresses out of each one.
> If you had an additional column such as contact_name or contact_no for
> each address line then you could do something like:
> SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
> MAX(CASE WHEN row_num = 2 THEN addr END),
> MAX(CASE WHEN row_num = 3 THEN addr END),
> ... etc
> FROM your_table
> GROUP BY contact_name ;
Thanks

Flatten File

I try to flatten a table.
in Access I would do this: iif(sequence=1,[Technology],Null) AS Tech1, iif(sequence=2,[Technology],Null) AS Tech2 FROM tblTechnologies GROUP BY Application
How do I flatten a file in SQL Server?
Thankslook up CASE in Books OnLine -- it does the same as the access IIF

rudy
http://rudy.ca/|||Great, Thanks. Step 1 completed.

Now I try to group the records so they all show up as 1 record with the primary key, but SQL want me to show list all fields i.e. GROUP BY itemID, sequence,Technology
In this case the fields will never show on one line.

Any suggestions?
Thanks..|||Hi Torgue

I just have a question. How can many records be grouped together and shown to all have the same Primary Key? If five records are grouped together (on sequence,Technology) each with there own Primary Key (itemID), which one of the five (itemID) do you choose to show when grouping?|||I'm sorry for the confusion. It is actually the Foreigh Key on the N-end of a 1:N relationship. The main table lists the applications and the N-side lists the technologies used for the application development.

In an Excel export I need to display the application with all technologies.

Like:
Application1 Tech1 Tech2 Tech2
Application2 Tech1 Tech2 Tech3

Thx..|||why don't you define the sql/server table in access as a linked table via odbc, and then write a crosstab query in access? i think you can easily export that to excel, too

in any case, if you do want to run this right in sql/server, you need
select application
, case when sequence=1
then technology else null end as tech1
, case when sequence=2
then technology else null end as tech2
from ... or something similar

rudy|||Thanks very much for your help.

Unfortunately I am using SQL Server as a web Backend, so I need to stick to SQL Server.

I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.|||I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.if you have already tried my CASE example, you will notice that each of the 1:N rows is present, and i apologize for not having shown how to summarize select application
, max( case when sequence=1
then technology else '' end ) as tech1
, max( case when sequence=2
then technology else '' end ) as tech2
from ...
group by applicationthe problem is, i don't know if you are doing any summing (is "technology" a char field?)

if you still cannot figure it out, please show your two table layouts

rudy|||works great, thank you very much.

technology is a character field, but the max takes the populated fields over the null fields.sql

flatfilesource(s) in a loop

I am trying to design a package to import the data of several .tx files into a table in sql server.

1) I created an execute task that truncates the sql server table i.e. truncate table tblContacts

2)
Placed a forrloop container with enumerator: foreach file enumerator
Folder points to the folder that holds the txt files
file: *.*
filename: fully qualified
variablemapping: User::FileName with Index 0

3)
placed a data flow task inside the forloop
this dataflow task has the following dataflow:
FlatFile Source: connection manager is pointing to one of the txt files
OLE DB Destination to place the txt data into tblContact in the database.

The question:
when the package is run, the tblContact gets populated only from the first txt file, i.e. the one which I placed in the flatfilesource connection manager.
How can I allow several files in the flatfilesource, instead of the one I have now...

Thanks

Firstly, in your For Each Loop, you could change filter from *.* to *.tx (not essential)

You need to make the connectionstring variable (ConnectionString is a property of your FlatFile connection Manager )

Click on your FlatFile Connection Manager (this is at the bottom of the BIDS screen).

Go to the properties of the CM, I mean the properties window that appears on the right of your screen.

Expand the Expressions property collection, and drill through to get the Property Expressions editor.

Choose property ConnectionString, and drill through the expression to get the Expression Builder.

Drag your variable (FileName) and drop it into the Expression box. I assume the FileName variable contains the entire file path and name.

Now it should work fine.

Flat file with a standard of 4

Hi

I am trying to import a flat file into a table, and from there select values from the table and insert the appropriate values into different tables

The flat file is pipe delimited. I.E

File Example:

01|Name|Surname|BenCode|Counter||||||DateTime

02|Name|Surname|BenCode|SchemeID|SchemeName|

03|Name|Surname|BenCode|ID||||Date_From|Date_To||||||||||

04|Name|Surname|BenCode|SchemeID|SchemeName||||CodeID|CodeDescription||

All these different fields are in one flat file. (It would be nice if they were in 4 seperate flat files but they're not)

I want to take the file, where the ID = 01 then the data must go into table Q1

WHERE the ID = 02 then the data must go into table Q2 and so on

When i tried to do it with SSIS, it started creating columns according to the file, but it takes the first row and counts only that rows fields and calculates the columns based on the firs record, but some of the records have more fields than that of the first row.

If i can just get this flat file imported into a single table then i can split the data up based on the table.

Any ideas will be welcome. I'm quite new to SSIS.

Kind Regards

Carel Greaves

To handle the varying number of columns, you can bring each row in as a single column, then parse it in a script component. By adding multiple outputs to the script task, you can send each record type to it's own unique output. Here's a few examples:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Flat file to table - rows out of order

Hi,

I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.

In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.

All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.

Is this normal?

Thanks

Is it normal? Well...its not not normal!

There is no concept of order in a database table. You should never assume that rows will get returned to you in the order that (you assume) they were inserted.

-Jamie

|||

That is not my understanding. For example, if you create a table, then insert a bunch of rows, one at a time, they will most definitely be returned in the order they were inserted. I have *never* seen an exception to this.

Perhaps the SSIS package is not inserting the rows in the order they are in the file?

Anyhow, I could be wrong, but this goes against my experience completely.

|||Not to sound mean or anything, but Jamie is absolutely right. There is no such thing as ordering in database land. Just because your experience "proves" otherwise, doesn't make it fact. The only way to guarantee order is to use an ORDER BY clause on your SQL statement which only controls the PRESENTATION of the data, not the way it's stored.

Do you have a situation that the records are out of order when ordering by an identity column, or are you merely using a "select * from table" statement without an ORDER BY clause?

This is perfectly normal behavior. You might want to add a sort transformation right before the destination. But still, there are no guarantees that the data will be stored "in order."|||By its definition, a database table is an unordered set of rows. While "most" of the time, a select without an ORDER BY clause will return the rows in the order they were entered, it is never guaranteed. The only way to guarantee retrieving rows in the order you want is with an ORDER BY clause on the query.|||

There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes.

Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things.

These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected.

There is no concept of order in a database table. Period.

-Jamie

|||

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

|||

sadie519590 wrote:

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

No worries. All the training courses in the world wouldn't have taught you this. The only way you learn a product is by using it. I've been using this damn thing for seven years now and I only know a fraction of it

-Jamie

Flat file to table

Hi,

I have a set of flat files and transforming it to SQL server. If I do that in 2000 it was done with in 45 seconds for 1.5 M records. If I do the same in SSIS it takes 3 minutes. Why there is difference in time that too lower when compared to the previous version. I used the data access mode as "Fast load". Am I missing anything while doing through SSIS?

There's so many "it depends" answers to this its not really worth posting a possible reason.

What exactly is the data flow doing? Where is the bottleneck?

-Jamie

|||

Its a very straight transformation. CSV file to a table and all the fields are set as Varchar,

- No validations made on the transformation

- No Calculations.

- No aggregations

again its a very straight transformation.

|||one thing i forget to mention. In 2000 I am using the global variable for looping the source files. In SSIS i used "For each loop" container.|||

And where is the bottleneck? Is it in sourcing the data or loading it to the target?

Check this out for tips on diagnosing bottlenecks:

http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx

-Jamie

|||

Jamie,

Thanks for sending the link, I will go through it in the evening as I am now in office. In the mean time I fixed and the performance is increased from 3 minutes to just 21 seconds (2000 took 45 seconds for the same transformation). The change I made is previously it was Native OLE DB but I changed it to MS OLE DB. If you find time could you please send any link or explain how this has created the dramatic change in performance.

Thanks for your time.

|||

I'm not sure what you mean by "native OLE DB". Can you send a link to the OLE DB driver that you were using?

-Jamie

|||

Jamie,

The link you provided was awesome. Thanks to Donald farmer for wonderful explanation and for you to identifing it to me on the right time.

Initially i had the provider as "Native OLE DB\SQL Native client" in the connection manager when it gives outpu on 3 minutes. When I changed this to "Native OLE DB \ Microsft OLE DB Provider for SQL server" it was processint the same task in less than 30 minutes. Is this due to the driver? how do i choose the best dirver?

|||

Dhanasu wrote:

...it was processint the same task in less than 30 minutes...

Based on your above comment, I'm assuming you mean "30 seconds" not 30 minutes.

|||Yes you're correct. it is 30 seconds.|||

That is an interesting observation. I would expect the opposite results, as SQL Native Client is the more recent provider.

It is almost certain that the difference lies in the used provider. I would try to ask why that is on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks.

sql