Derived table and adding another column problem

Hi
I have this query below, which I'm trying to add and group data on the w
number (ISO w, taken from Books online) fn_getISOW returns an integer
and works independently of this query. This query also works as I want when
there is no wno invloved (the 3 places)
The error I get is "Invalid column name 'wno'", I think what I'm doing is
a "copy" of what has been done in the count(case.....) as xx
Can anyone see what I'm doing wrong and how to fix it?
regards
Henry
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
SET DATEFORMAT mdy
set @.site = 1
set @.calltype = 'E'
set @.ext = '6810'
SELECT wno***, network, centre, center, team, total, besvaret, optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0 THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY wno***, network, centre, center, team) AS D
ORDER BY 1Try this
SELECT wno***, network, centre, center, team, total, besvaret,
optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0
THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as
wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid)
AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
) AS D
GROUP BY wno***, network, centre, center, team ORDER BY 1
Madhivanan|||> Try this
> Madhivanan
>
Hi
No I'm sorry that doesn't do the trick either :(
regards
Henry|||> > Try this
> Hi
> No I'm sorry that doesn't do the trick either :(
> regards
> Henry
Solved... I had to use the same syntax in the group by part since wno is
not know at that time.
regards
Henrik

Derived Column Transformation

Hello.
I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer.
Here is the expression I am using for calculating the age:
(DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25)

In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation.
I get the following error when executing my package:

Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.
Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Any assistnace would be greatly appreciated.
I've come across a similar issue before. It seems that derived column transformation doesn't like converting char or integer data to dt_dbtimestamp. The way I got around it was using a lookup to a date dimension on the date in char format and pulling back the corresponding datetime format for the record. Is this a possibility?|||

Thank you for your reply.

I guess there are other options I can choose from. However, since this is a SSIS component, I would like to make it work and find out why is it so difficult to deal with. I understand that the problem is data type, nevertheless, the data is already in SQL and it is using SQL data type. If I were importing data from a flat file, I would accept the fact that data type could be more of an issue.

When I run my SQL query (please see two versions), it works like a charm.:

select convert(int,datediff("dd", convert(datetime,DOB),getdate())/365.25) FROM [DATABASE].[dbo].[TABLENAME]

OR

select cast(datediff("day",cast(DOB as datetime),cast([Service Date] as datetime))/365.25 as int) from FROM [DATABASE].[dbo].[TABLENAME]

|||

What format is your date field thats stored as char?

I just tried '20070101' and it didn't work but '01012007' works

|||

Hi there:
Once again, thank you for your reply.

The format I use in SQL is YYYYMMDD; Example: 19900925

|||

Try converting it around with some string functions...

Code Snippet

(DT_DBTIMESTAMP)(RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4))

|||

Hi there:
I will try your suggestion and I will let you know.

|||

Hi there:

I did tried your suggestion. Here is what I wrote:

(DATEDIFF("DAY",(DT_DBTIMESTAMP)(SUBSTRING(DOB,7,2) + SUBSTRING(DOB,5,2) + SUBSTRING(DOB,1,4)),(DT_DBTIMESTAMP)(SUBSTRING([Service Date],7,2) + SUBSTRING([Service Date],5,2) + SUBSTRING([Service Date],1,4))) / 365.25)

I am still having the same error:

Error: 0xC0049067 at Data Flow Task, Derived Column [3949]: An error occurred while evaluating the function.

Error: 0xC0209029 at Data Flow Task, Derived Column [3949]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (3949)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (4008)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

|||

Alright, just did some more testing. For some reason you can't go from char to datetime, you have to go from char to int to datetime. Also, the int format has to be mmddyyyy

Code Snippet

(DT_DBTIMESTAMP)((DT_I4)((DT_STR,8,1252)RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4)))

|||

Hi there:
Unfortunately I tried your last suggestion and I still cannot process the package. I did put a data viewer between my SQL DB Source and the Derived Column and the DOB format comes as 19900921 which is what I have in my DB.

I agree with you that there is something with the conversion that is not working.

I tried the following cast in SQL using the following format(ddmmyyyy):

select cast('20092007'as datetime)--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I wonder if the way CAST works is what is causing the problem.

By the way, thank you for assisting me. Appreciate your time and effort

|||

I used the following in a variable and it worked:

(DT_I8) (DATEDIFF("DAY",(DT_DBTIMESTAMP) @.[User::CurrentDate], (DT_DBTIMESTAMP) @.[User::QuotaStartDt] )/365 )

The variables I pass into the expression are in the standard Datetime format. The result I get is a 0 but it did not give me any errors.

|||Hi there:
Thank you for your suggestion.

My DOB string is formatted as: yyyymmdd and casting a string to datetime using this format works fine in SQL. If the format is ddmmyyyy, then casting does not work at all, unless I cast the string several times.

I tried using substring, casting etc and I have not seen any positive result. I think I will perform the age calculation in sql and added to my query. Too bad something simple gets so complicated....!

I would like to thank you all for your time and effort.

Derived Column Transformation

Hello.
I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer.
Here is the expression I am using for calculating the age:
(DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25)

In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation.
I get the following error when executing my package:

Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.
Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Any assistnace would be greatly appreciated.
I've come across a similar issue before. It seems that derived column transformation doesn't like converting char or integer data to dt_dbtimestamp. The way I got around it was using a lookup to a date dimension on the date in char format and pulling back the corresponding datetime format for the record. Is this a possibility?|||

Thank you for your reply.

I guess there are other options I can choose from. However, since this is a SSIS component, I would like to make it work and find out why is it so difficult to deal with. I understand that the problem is data type, nevertheless, the data is already in SQL and it is using SQL data type. If I were importing data from a flat file, I would accept the fact that data type could be more of an issue.

When I run my SQL query (please see two versions), it works like a charm.:

select convert(int,datediff("dd", convert(datetime,DOB),getdate())/365.25) FROM [DATABASE].[dbo].[TABLENAME]

OR

select cast(datediff("day",cast(DOB as datetime),cast([Service Date] as datetime))/365.25 as int) from FROM [DATABASE].[dbo].[TABLENAME]

|||

What format is your date field thats stored as char?

I just tried '20070101' and it didn't work but '01012007' works

|||

Hi there:
Once again, thank you for your reply.

The format I use in SQL is YYYYMMDD; Example: 19900925

|||

Try converting it around with some string functions...

Code Snippet

(DT_DBTIMESTAMP)(RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4))

|||

Hi there:
I will try your suggestion and I will let you know.

|||

Hi there:

I did tried your suggestion. Here is what I wrote:

(DATEDIFF("DAY",(DT_DBTIMESTAMP)(SUBSTRING(DOB,7,2) + SUBSTRING(DOB,5,2) + SUBSTRING(DOB,1,4)),(DT_DBTIMESTAMP)(SUBSTRING([Service Date],7,2) + SUBSTRING([Service Date],5,2) + SUBSTRING([Service Date],1,4))) / 365.25)

I am still having the same error:

Error: 0xC0049067 at Data Flow Task, Derived Column [3949]: An error occurred while evaluating the function.

Error: 0xC0209029 at Data Flow Task, Derived Column [3949]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (3949)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (4008)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

|||

Alright, just did some more testing. For some reason you can't go from char to datetime, you have to go from char to int to datetime. Also, the int format has to be mmddyyyy

Code Snippet

(DT_DBTIMESTAMP)((DT_I4)((DT_STR,8,1252)RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4)))

|||

Hi there:
Unfortunately I tried your last suggestion and I still cannot process the package. I did put a data viewer between my SQL DB Source and the Derived Column and the DOB format comes as 19900921 which is what I have in my DB.

I agree with you that there is something with the conversion that is not working.

I tried the following cast in SQL using the following format(ddmmyyyy):

select cast('20092007'as datetime)--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I wonder if the way CAST works is what is causing the problem.

By the way, thank you for assisting me. Appreciate your time and effort

|||

I used the following in a variable and it worked:

(DT_I8) (DATEDIFF("DAY",(DT_DBTIMESTAMP) @.[User::CurrentDate], (DT_DBTIMESTAMP) @.[User::QuotaStartDt] )/365 )

The variables I pass into the expression are in the standard Datetime format. The result I get is a 0 but it did not give me any errors.

|||Hi there:
Thank you for your suggestion.

My DOB string is formatted as: yyyymmdd and casting a string to datetime using this format works fine in SQL. If the format is ddmmyyyy, then casting does not work at all, unless I cast the string several times.

I tried using substring, casting etc and I have not seen any positive result. I think I will perform the age calculation in sql and added to my query. Too bad something simple gets so complicated....!

I would like to thank you all for your time and effort.

sql

Derived column returning no data on GetDate() function

Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?

Hi Jonathan,

That's very odd indeed. Have you by any chance changed the type of the derived column, or is it still DT_DBTIMESTAMP?
Are you seeing any errors at all?

Thanks
Mark

|||

Hi Mark--No errors, and no change to the data type. Just a return of a NULL value on each row in the result set after stored in the local database.

I'm running through an online example now using the exact same GetDate() function with the AdventureWorks database now--not finished with the example yet on another machine installed with SQL Server 2005 SP1 Developer Edition, so I can't say whether this is reproducible yet.

- Jonathan

|||If you put a data viewer on the path between the derived column and whatever is after it, is the result already NULL at that point?|||

Hi Mark--I'm not sure when it returns NULL; I didn't have a data viewer on hand to do so. I'm new to Visual Studio and Integration Services, so I know that I need to isolate the contents of the derived column as a variable at the time the step is executed, but am not sure how to do that in this product. What data viewer utility or resource would you suggest?

Jonathan

|||

If you double click on any of the path arrows between components, it will bring up the "Data Flow Path Editor" dialog. In the left pane, you should see a selection called "Data Viewers". Select that, and then hit the add button, and you should see a dialog with a few data viewer choices. For this purpose, "Grid" is what you want.

Once you have added a Grid data view to the path that exits the derived column, you can execute the package again (within BIDS) and you should get a dialog that shows a number of rows with all the columns and data values at that point, and execution will pause there. If the column is already showing as NULL in this viewer, then we'll have isolated the problem to the derived column, most likely.

Let me know what you find out.

Thanks
Mark

sql

Derived column returning no data on GetDate() function

Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?

Hi Jonathan,

That's very odd indeed. Have you by any chance changed the type of the derived column, or is it still DT_DBTIMESTAMP?
Are you seeing any errors at all?

Thanks
Mark

|||

Hi Mark--No errors, and no change to the data type. Just a return of a NULL value on each row in the result set after stored in the local database.

I'm running through an online example now using the exact same GetDate() function with the AdventureWorks database now--not finished with the example yet on another machine installed with SQL Server 2005 SP1 Developer Edition, so I can't say whether this is reproducible yet.

- Jonathan

|||If you put a data viewer on the path between the derived column and whatever is after it, is the result already NULL at that point?|||

Hi Mark--I'm not sure when it returns NULL; I didn't have a data viewer on hand to do so. I'm new to Visual Studio and Integration Services, so I know that I need to isolate the contents of the derived column as a variable at the time the step is executed, but am not sure how to do that in this product. What data viewer utility or resource would you suggest?

Jonathan

|||

If you double click on any of the path arrows between components, it will bring up the "Data Flow Path Editor" dialog. In the left pane, you should see a selection called "Data Viewers". Select that, and then hit the add button, and you should see a dialog with a few data viewer choices. For this purpose, "Grid" is what you want.

Once you have added a Grid data view to the path that exits the derived column, you can execute the package again (within BIDS) and you should get a dialog that shows a number of rows with all the columns and data values at that point, and execution will pause there. If the column is already showing as NULL in this viewer, then we'll have isolated the problem to the derived column, most likely.

Let me know what you find out.

Thanks
Mark

Derived Column can't add 8-byte integers

I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:

Column Name: BIG_ID

Derived Column: < add as new column >

Expression: (DT_I8)[ID] + 840230000538058

Data Type: eight-byte signed integer {DT_I8]

The error I get:

The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.

Then I try the expression:

(DT_I8)[ID] + (DT_I8)840230000538058

and

[ID] + 840230000538058

and get the same error.

What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?

Thanks,

Michael

I'm able to make this work by putting the large integer into a variable that's been typed as Int64. However, I'm still curious as to why I shouldn't be able to do this in the derived column transformation without using a variable?

Thanks,

Michael

|||Looks like a bug to me. I get the same behavior. Maybe someone from MS could check, or one of the MVPs could flag this for follow-up.|||

When you specify a numeric literal in an expression, it is by default typed as a DT_I4. If you want it to be a DT_I8, you must append an 'L' to the literal. For DT_UI8, append 'UL'.

So for your example, you should try the following expression: (DT_I8)[ID] + 840230000538058L

See the Books Online topic "Literals (SSIS)" for more details.

Thanks

Mark

|||Doh! That would explain it |||

Thanks!

-Michael

Deploying the whole lot

Hi All,

I am adding a reporting suite to our application based upon SSRS 2005 and I am looking for a comprehensive example of how I go about deploying this.

Essentially I want to automate the entire process:

Create the DB, Create the Virtual Dirs, add my reports etc.

I have looked at the documentation and it is limited to say the least. I went down the route of attempting to script the whole thing and after 3 days of it I was still stuck on a few problems so I was hoping that someone knows of an example of how to go through all of these processes.

An example of the problems I ran into is that the "CreateVirtualDir" method on the reporting services WMI objects simply didn't work. It didn't return an error but it didn't create the Dir either!

The documentation around this method and most others is ambiguous and contains no examples.

I then went down the route of using IIS WMI to create the dirs which worked but then I was unable to update the Reporting Services configuration to pick this up without resorting to changing the config files directly - and even when I did this they didn't always seem to be picked up.

So at present instead of giving our guys in deployment a 1 click install they have a 9 page document explaining the install and set up of Reporting services from start to finish.

Any help or more thorough documentation would be greatly appreciated.

thanks,

Dhugal.

PS: Just to add I have already altered our install to install the RS module along with SQL Server so that bit is covered. I just want to script the entire configuration process.

Bump ^ ^

Deploying cube changes to multiple sites

Hi,

How does one deploy cube changes (say adding a new field into a dimension) into multiple customer databases without overwriting their Kpis?

We have a SSAS application which will snap into our existing application and allow customers to define Environmental, Health & Safety Kpis. Each customer will have different Kpis, so how do we deploy incremental updates, without overwriting those Kpis?

I know we can download a customers SSAS database into a new VS project, and copy the Kpis into an XMLA deployment script, but that is a lot of work for each customer.

What I am hoping to find is something like a SQL Script that makes the schema changes, but does not touch the data (Kpis etc.). Of course, my SQL knowledge has only got me into trouble in SSAS, so who knows.

Thanks,

--Phil

Probably the best approach would be to write a script which uses DISCOVER_XML_METADATA to retrieve the cube's metadata, and then changes the XML my injecting your KPIs and then sending this XMLA script inside <Alter> command.