ETL / Data mart / Cube question

We are in process of using new Kimball toolkit book as our guide to create another DM. Since we only have one data source, we think we need to pull in all the tables into the staging area. At first we wanted to just pull in minimal data to populate the Dimension/Fact tables and any information needed to derive columns.
However the business area wants a complete snapshot of ALL THE data for this given subject matter. Their reason is that sometimes our CUBES don't have all the information they want, and they want to be able to query the data mart tables against the staging area tables using SQL or Access. Since our data mart load times is first Saturday of the month - this argues in favor of this. If the load process was every day or once a week - it might be better to just query across the data mart and the reporting (replicated production) database - the the data mart tables would be older than the information they were linking against on the replicated report DB using the 1st Saturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Staging to OLAP DB - which is really just another OLTP DB but the tables are modeled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access, has a big manual step. After all the ETL happens and the Dim/Fact tables have been updated - we are having to open up SSAS and process the cubes, the first business day after the 1st Saturday, to get them refreshed - can't this be automated within SSIS?
4. There have been some major production changes to our existing data source for our existing production data mart. All the ETL was done on SQL2k with stored proc's and DTS was only used as the policeman to control the flow. All the SCD processes were done and are working fine using SP's. We now have to make changes and are faced with having two complete different systems to manage - one with an ETL using the old method and the 2nd using SSIS. Some of the CUBES from the original data mart are going to be combined with our new cubes we are creating in our new system. To try and make it clearer - we have for example a Client dimension and a Client fact table that first is refreshed monthly with the old ETL, and now our new subject matter is now also going to affect the Client dimension and Client fact table that was created the first project. The old production changes it appears may be many weeks to adjust using the old ETL methods - so it begs the question, since all the ETL is working fine with DTS as flow master - is it perhaps better to get it all working with SSIS calling all the SP's and incorporate all our new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL package or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services Administration
I think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> We are in process of using new Kimball toolkit book as our guide to create another DM. Since we only have one data source, we think we need to pull in all the tables into the staging area. At first we wanted to just pull in minimal data to populate the Dimension/Fact tables and any information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE data for this given subject matter. Their reason is that sometimes our CUBES don't have all the information they want, and they want to be able to query the data mart tables against the staging area tables using SQL or Access. Since our data mart load times is first Saturday of the month - this argues in favor of this. If the load process was every day or once a week -it might be better to just query across the data mart and the reporting (replicated production) database - the the data mart tables would be older than the information they were linking against on the replicated report DB using the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to Staging to OLAP DB - which is really just another OLTP DB but the tables aremodeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to access, has a big manual step. After all the ETL happens and the Dim/Fact tables have been updated - we are having to open up SSAS and process the cubes, the first business day after the 1st Saturday, to get them refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing data source for our existing production data mart. All the ETL was done on SQL2k with stored proc's and DTS was only used as the policeman to control the flow. All the SCD processes were done and are working fine using SP's. We now have to make changes and are faced with having two complete different systems to manage - one with an ETL using the old method and the 2nd using SSIS. Some of the CUBES from the original data mart are going to be combined with our new cubes we are creating in our new system. To try and make it clearer - we have for example a Client dimension and a Client fact table that first is refreshed monthly with the old ETL, and now our new subject matter is now also going to affect the Client dimension and Client fact table that was created the first project. The old production changes it appears may be many weeks to adjust using the old ETL methods - so it begs the question, since all the ETL is working fine with DTS as flow master - is it perhaps better to get it all working with SSIS calling all the SP's and incorporate all our new ETL within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL package or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:

>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>
|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:

ETL / Data mart / Cube question

We are in process of using new Kimball toolkit book as our guide t
o create another DM. Since we only have one data source, we think we need t
o pull in all the tables into the staging area. At first we wanted to just
pull in minimal data to populate the Dimension/Fact tables and any informati
on needed to derive columns.
However the business area wants a complete snapshot of ALL THE dat
a for this given subject matter. Their reason is that sometimes our CUBES d
on't have all the information they want, and they want to be able to query t
he data mart tables against the staging area tables using SQL or Access. Si
nce our data mart load times is first Saturday of the month - this argues in
favor of this. If the load process was every day or once a week - it might
be better to just query across the data mart and the reporting (replicated p
roduction) database - the the data mart tables would be older than the infor
mation they were linking against on the replicated report DB using the 1st S
aturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Stag
ing to OLAP DB - which is really just another OLTP DB but the tables are mod
eled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access,
has a big manual step. After all the ETL happens and the Dim/Fact tables ha
ve been updated - we are having to open up SSAS and process the cubes, the f
irst business day after the 1st Saturday, to get them refreshed - can't this
be automated within SSIS?
4. There have been some major production changes to our existing data
source for our existing production data mart. All the ETL was done on SQL2k
with stored proc's and DTS was only used as the policeman to control the fl
ow. All the SCD processes were done and are working fine using SP's. We no
w have to make changes and are faced with having two complete different syst
ems to manage - one with an ETL using the old method and the 2nd using SSIS.
Some of the CUBES from the original data mart are going to be combined wit
h our new cubes we are creating in our new system. To try and make it clear
er - we have for example a Client dimension and a Client fact table that fir
st is refreshed monthly with the old ETL, and now our new subject matter is
now also going to affect the Client dimension and Client fact table that was
created the first project. The old production changes it appears may be m
any weeks to adjust using the old ETL methods - so it begs the question, sin
ce all the ETL is working fine with DTS as flow master - is it perhaps bette
r to get it all working with SSIS calling all the SP's and incorporate all o
ur new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL packag
e or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services AdministrationI think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> =B7 We are in process of using new Kimball toolkit book as our gu=
ide to create another DM. Since we only have one data source, we think we =
need to pull in all the tables into the staging area. At first we wanted t=
o just pull in minimal data to populate the Dimension/Fact tables and any i=
nformation needed to derive columns. =20
> =B7 However the business area wants a complete snapshot of ALL TH=
E data for this given subject matter. Their reason is that sometimes our C=
UBES don't have all the information they want, and they want to be able to =
query the data mart tables against the staging area tables using SQL or Acc=
ess. Since our data mart load times is first Saturday of the month - this =
argues in favor of this. If the load process was every day or once a week -=
it might be better to just query across the data mart and the reporting (r=
eplicated production) database - the the data mart tables would be older th=
an the information they were linking against on the replicated report DB us=
ing the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach? =20
> 2. According to a Microsoft diagram, the load goes from Source to S=
taging to OLAP DB - which is really just another OLTP DB but the tables are=
modeled as Dimension/Fact tables, right? =20
> 3. Our existing data mart which everyone mostly uses CUBES to acces=
s, has a big manual step. After all the ETL happens and the Dim/Fact table=
s have been updated - we are having to open up SSAS and process the cubes, =
the first business day after the 1st Saturday, to get them refreshed - can'=
t this be automated within SSIS?
> 4. There have been some major production changes to our existing da=
ta source for our existing production data mart. All the ETL was done on S=
QL2k with stored proc's and DTS was only used as the policeman to control t=
he flow. All the SCD processes were done and are working fine using SP's. =
We now have to make changes and are faced with having two complete differe=
nt systems to manage - one with an ETL using the old method and the 2nd usi=
ng SSIS. Some of the CUBES from the original data mart are going to be com=
bined with our new cubes we are creating in our new system. To try and mak=
e it clearer - we have for example a Client dimension and a Client fact tab=
le that first is refreshed monthly with the old ETL, and now our new subjec=
t matter is now also going to affect the Client dimension and Client fact t=
able that was created the first project. The old production changes it ap=
pears may be many weeks to adjust using the old ETL methods - so it begs th=
e question, since all the ETL is working fine with DTS as flow master - is =
it perhaps better to get it all working with SSIS calling all the SP's and =
incorporate all our new ETL within SSIS using the new features that SSIS ca=
n offer? =20
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL pac=
kage or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:

>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:
>

Establish log dhipping db in sql2005

Hi all
I have db on my log shipping server as standby\readonly and my transaction log
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advancedfrank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced

Establish log dhipping db in sql2005

Hi all
I have db on my log shipping server as standby\readonly and my transaction log
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advanced
frank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced

Establish log dhipping db in sql2005

Hi all
I have db on my log shipping server as standby\readonly and my transaction l
og
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advancedfrank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced

escape single qoute

Is there a way to escape single quotes ' in a sql statement that uses a sql data source that is automated? I know you can do it with a string manip and replacing them with double single quotes. I am just looking for a simple way.

Thanks

Adam

If you are using parameters, you don't have to worry about single quotes in strings because SQL Server preserves the input data as it is.

If you aren't using parameters, you'll have to use two single quotes.

|||

You need to use two single quotes.

Errors using multiple parameters in a SQL Statement

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||

I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.

Here is how:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756434&SiteID=1

|||

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

|||

Rafael Salas wrote:

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.

Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".

The issue is likely as Rafael pointed out if you are using a complex query.|||

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

|||

Mike Batton wrote:

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

Can you post your query?|||

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

|||

Mike Batton wrote:

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

Use question marks - always, and don't number them.

select * from tew_d_dt where dt_key = ? and cal_yr_id = ?

Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

|||

Mike Batton wrote:

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||

What about in the case part of a select statement... should that work or not?

Below is part of the actual query that we're trying to get to work.

Mike

select 'ent' AS level_desc,

sku.new_concept_cd AS code_1,

sku.color AS color_name,

case when sls.date_key = ?

then

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) as sales_life,

from edw.jjwhse.tew_l_stock_status_sku sku

left outer join

edw_jj.sales.vew_r_sls_sku_loc_wk sls

on sls.sku_key = sku.sku_key

where sls.date_key <= ?

and sku.sku_type_desc = 'sales'

and sls.loc_key not in (6,9,10,11,12,13,14,106)

and sku.new_concept_cd is not null

group by sku.new_concept_cd, sku.color, sls.date_key

|||

Mike Batton wrote:

What about in the case part of a select statement... should that work or not?

Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||

Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.

Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.

Errors processing SSAS 2005 Cube against SQL 2000 database

Hi,

I am receiving the following erros when i try to deploy/process my cube. The source db is Sql Server 2000, could this be the issue? I have a connection created (have tried both OLDB providers - SQL Native client and MS OLDB provider) and I am supplying a sql server user name and pass word (datareader permission in db). The 2005 SSAS service is running under a local user account so authentication is not possible using that account. And I have tried various settings under "Impersonation Information" with no luck?

SQL 2K5 sp1 on Win XP sp2

Sql 2k (sp4) on Win2k server

Thanks!

Build started: Project: Aegis, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Aegis, Configuration: Development

Performing a full deployment of the 'Aegis' database to the 'JWILAMETZ\SQLSERVER2005_01' server.

Generating deployment script...

Add Database Aegis

Process Database Aegis

Done

Sending deployment script to the server...

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY CD' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY DESC' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'DATE ENTERED' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'EXCHANGE RATE' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Deploy complete -- 16 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

First try and see if you connect to your SQL Server 2000 from AS machine.

Login into AS machine under credentials of your local user account and try connecting to your SQL Server machine.

The credentals many customers choosing for their Data Source object is ImpersonateServiceAccount.

Please note. Once you saved user name and password as part of your connection sting, you will have to re-submit username and password every time you update your datasource.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Willajo,

can you please let me know how could you solve? I am getting the same error.

thanks a lot in advance...

Errors processing SSAS 2005 Cube against SQL 2000 database

Hi,

I am receiving the following erros when i try to deploy/process my cube. The source db is Sql Server 2000, could this be the issue? I have a connection created (have tried both OLDB providers - SQL Native client and MS OLDB provider) and I am supplying a sql server user name and pass word (datareader permission in db). The 2005 SSAS service is running under a local user account so authentication is not possible using that account. And I have tried various settings under "Impersonation Information" with no luck?

SQL 2K5 sp1 on Win XP sp2

Sql 2k (sp4) on Win2k server

Thanks!

Build started: Project: Aegis, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Aegis, Configuration: Development

Performing a full deployment of the 'Aegis' database to the 'JWILAMETZ\SQLSERVER2005_01' server.

Generating deployment script...

Add Database Aegis

Process Database Aegis

Done

Sending deployment script to the server...

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY CD' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY DESC' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'DATE ENTERED' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'EXCHANGE RATE' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Deploy complete -- 16 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

First try and see if you connect to your SQL Server 2000 from AS machine.

Login into AS machine under credentials of your local user account and try connecting to your SQL Server machine.

The credentals many customers choosing for their Data Source object is ImpersonateServiceAccount.

Please note. Once you saved user name and password as part of your connection sting, you will have to re-submit username and password every time you update your datasource.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Willajo,

can you please let me know how could you solve? I am getting the same error.

thanks a lot in advance...

Errors processing data from Oracle to SQL

hi here′s a new one.....
i′ve created a dts that is using an odbc source to connect to an oracle server, the conexion works just fine and i have no problems with it, then, i run this package from mi computer and the data transfer ends succesfully, when i upload it to my server in SQL 2005 and set it into a job.. i get errors like these:

Event Name: OnError
Message: Thread "WorkThread0" has exited with error code 0xC0047039.

Event Name: OnError
Message: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Event Name: OnError
Message: The PrimeOutput method on component "table" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Event Name: OnError
Message: The component "faccomitecedidos" (1) was unable to process the data.

Event Name: OnError
Message: The "component "table" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "diasperm" (1707)" specifies failure on error. An error occurred on the specified object of the specified component.

why is this happening , any solutions or ideas? the data i want to extract comes from an sql command not a table
this is the query:

SELECT
FCNSS ,
FITIPOSOLICITUD ,
FIFOLIO ,
RTRIM(FCNOMBRE)||' '||RTRIM(FCAPPATERNO)||' '||RTRIM(FCAPMATERNO) AS NOMBREAFILIADO,
fdfinicta as FAFIL ,
fdFecCedido as FCED ,
(fdFecCedido-fdfinicta) AS diasperm ,
FNSALARIOACTUAL AS SALAFIL ,
(SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED ,
(SELECT DISTINCT (FISDI/48.60) FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS CalSalCED ,
FCNUMPROMOTOR AS cod_promotor,
(SELECT RTRIM(FCNOMBRES)||' '||RTRIM(FCAPEPATERNO)||' '||RTRIM(FCAPEMATERNO)
FROM gentec_own.prommaestro where FCNUMPROMOTOR = gentec_own.faccomite.FCNUMPROMOTOR AND ROWNUM = 1) AS NOMPROMOTOR,
FICVEENTCED as aforeorig ,
FCAFORECEDIDO as aforeced ,
FNINGCOMT ,
FNCTOPROMOCION ,
FNCTOADMON ,
FNCONTRIBUCION ,
fcCanal as Canal ,
FCDIVISION as Division ,
FCREGION as Gerencia
FROM gentec_own.FACCOMITE
WHERE FCCEDIDO = 1
and (to_char(fdFecCedido,'yyyymmdd')>=to_char(sysdate-8,'yyyymmdd') and to_char(fdFecCedido,'yyyymmdd')< to_char(sysdate,'yyyymmdd'))
order by fdFecCedido;

please!!!1 somebody

maybe the problem is here:

(fdFecCedido-fdfinicta) AS diasperm

|||

hi unfortunately this wasn′t the error, i tried changing the query for a view in the oracle server but the error is stil the same

Event Name: OnError

Message: The "component "DataReader Source" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "DIASPERM" (404)" specifies failure on error. An error occurred on the specified object of the specified component.

and the query is just :

select * from db.view

and that′s it.... any posible solutions? is there any complications if the server where i′m running the job is under 64 bits and the dts ′s been created under 32 bits? if so. where can i find a 32 bits odbc driver for oracle that i may install in the target server!!!! please!!!!!!!!!!

|||

It looks like a SQL syntax problem. If you try to issue your entire original statement DIRECTLY against your oracle database, do you not get an error?

It seems to me that you need to tie back your results to the main query as you did here:

(SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED ,

|||

i don′t think this is a problem of syntax cause i run the query directly on the oracle db and it works, it seems more like a problem of windows version, since i run this dts on my 32 bits pc i get the results without problems.. the problem comes when uploading the package to the target server that is running under 64 bits, to do this i'm using an 32bits odbc oracle driver but the odbc that is used in the target server seems to be under 64 bits , because of this, i'd like to try using the same kind of odbc so, where can i get
one?

a 32 bits odbc driver for oracle that works in a server running in 64 bits mode

pleaseee

Errors on importing a text file

When a DTS fails on a Text Source input with an error like "DTS_Transformation encountered an invalid data value for 'Column1' destination"

Is there a way to get the line number of the textfile where the import failed? It is hard to determine where in my 40,000-line file it found the invalid value for my column.

Thanks,
AndrewSpecify the LOG file in Package Properties/Logging.|||WHere does it store the logs on the server?|||Wherever you would like it to be.|||I set the error log file to a text file on my esktop - it seems to repeat the same error message (without the line number) - am i missing something?

Errors on Datasource.aspx page

I have an installation of RS 2000 on a Win 2003 server.
I've started getting an error whenever I access the Data Source pages
using the Report Manager.
The page loads OK. But I cannot make any changes. For example, if I try
to change the "Connect Using" options from "Windows Integrated
Secuirty" to "Credentials stored securely ...", the radio button shows
the selection, but the required text boxes are not enabled, In the
bottom left corener of the IE status bar, "Error on Page" flashes.
The server log files and event logs show nothing regarding the error.
This happens on all Data Source pages on this server.

Any ideas?

It sounds like some of the javascript files are missing. Try reregistering asp.net client files by running aspnet_regiis -c from \windows\microsoft.net\framework\v1.1.4322.

Errors in relational and OLAP Engine

Hi Edward,

You told me to look into my data source view yesterday. In my data source view i have the dbo.Prescription instead of dbo_Prescription though the error message indicates that the dbo_Prescription table is missing in the data source view.

as below.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Analysis Services Project1</DatabaseID>
<CubeID>MSCPROJECT</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'Services' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:31 AM; Duration: 0:00:00
Processing Dimension Attribute 'Service Title' completed successfully. 36 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Services].[Service_Title] AS [dbo_ServicesService_Title0_0]
FROM [dbo].[Services] AS [dbo_Services]
Processing Dimension Attribute 'Service Order' completed successfully. 18 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Services].[Service_Order] AS [dbo_ServicesService_Order0_0]
FROM [dbo].[Services] AS [dbo_Services]
Processing Dimension Attribute 'Services' completed successfully. 36 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Services].[ServiceId] AS [dbo_ServicesServiceId0_0],[dbo_Services].[Service_Title] AS [dbo_ServicesService_Title0_1],[dbo_Services].[Service_Order] AS [dbo_ServicesService_Order0_2]
FROM [dbo].[Services] AS [dbo_Services]
Processing Dimension 'Severity' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:31 AM; Duration: 0:00:00
Processing Dimension Attribute 'Severity Name' completed successfully. 6 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Severity].[SeverityName] AS [dbo_SeveritySeverityName0_0]
FROM [dbo].[Severity] AS [dbo_Severity]
Processing Dimension Attribute 'Severity' completed successfully. 6 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Severity].[SeverityCode] AS [dbo_SeveritySeverityCode0_0],[dbo_Severity].[SeverityName] AS [dbo_SeveritySeverityName0_1]
FROM [dbo].[Severity] AS [dbo_Severity]
Processing Dimension 'Project' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:31 AM; Duration: 0:00:00
Processing Dimension Attribute 'Project Name' completed successfully. 4 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Project].[ProjectName] AS [dbo_ProjectProjectName0_0]
FROM [dbo].[Project] AS [dbo_Project]
Processing Dimension Attribute 'Project' completed successfully. 4 rows have been read.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Project].[ProjectId] AS [dbo_ProjectProjectId0_0],[dbo_Project].[ProjectName] AS [dbo_ProjectProjectName0_1]
FROM [dbo].[Project] AS [dbo_Project]
Processing Dimension 'Initial Visit' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:31 AM; Duration: 0:00:00
Processing Dimension Attribute 'Physician Id' completed successfully. 8 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_InitialVisit].[PhysicianId] AS [dbo_InitialVisitPhysicianId0_0]
FROM [dbo].[InitialVisit] AS [dbo_InitialVisit]
Processing Dimension Attribute 'Nurse ID' completed successfully. 7 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_InitialVisit].[NurseID] AS [dbo_InitialVisitNurseID0_0]
FROM [dbo].[InitialVisit] AS [dbo_InitialVisit]
Processing Dimension Attribute 'Initial Visit' completed successfully. 51 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_InitialVisit].[PatientId] AS [dbo_InitialVisitPatientId0_0],[dbo_InitialVisit].[Date] AS [dbo_InitialVisitDate0_1],[dbo_InitialVisit].[PhysicianId] AS [dbo_InitialVisitPhysicianId0_2],[dbo_InitialVisit].[NurseID] AS [dbo_InitialVisitNurseID0_3]
FROM [dbo].[InitialVisit] AS [dbo_InitialVisit]
Processing Dimension 'Medicine' completed successfully.
Start time: 6/21/2006 9:41:31 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
Processing Dimension Attribute 'Name' completed successfully. 19 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Medicine].[Name] AS [dbo_MedicineName0_0]
FROM [dbo].[Medicine] AS [dbo_Medicine]
Processing Dimension Attribute 'Medicine' completed successfully. 20 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Medicine].[MedicineCode] AS [dbo_MedicineMedicineCode0_0],[dbo_Medicine].[Name] AS [dbo_MedicineName0_1]
FROM [dbo].[Medicine] AS [dbo_Medicine]
Processing Dimension 'Diagnosis' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
Processing Dimension Attribute 'Diagnosis Name' completed successfully. 5 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[Diagnosis name] AS [dbo_DiagnosisDiagnosis_x0020_name0_0]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension Attribute 'Description' completed successfully. 4 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[Description] AS [dbo_DiagnosisDescription0_0]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension Attribute 'Medicine Code' completed successfully. 5 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[MedicineCode] AS [dbo_DiagnosisMedicineCode0_0]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension Attribute 'Diagnosis' completed successfully. 5 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[DiagnosisCode] AS [dbo_DiagnosisDiagnosisCode0_0],[dbo_Diagnosis].[Diagnosis name] AS [dbo_DiagnosisDiagnosis_x0020_name0_1],[dbo_Diagnosis].[Description] AS [dbo_DiagnosisDescription0_2],[dbo_Diagnosis].[MedicineCode] AS [dbo_DiagnosisMedicineCode0_3]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension 'Patient' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
Processing Dimension Attribute 'DateOfBirth' completed successfully. 49 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[DateOfBirth] AS [dbo_PatientDateOfBirth0_0]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Dimension Attribute 'Gender' completed successfully. 3 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[Gender] AS [dbo_PatientGender0_0]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Dimension Attribute 'PhoneNumber' completed successfully. 46 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[PhoneNumber] AS [dbo_PatientPhoneNumber0_0]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Dimension Attribute 'PatientId' completed successfully. 50 rows have been read.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[PatientId] AS [dbo_PatientPatientId0_0],[dbo_Patient].[DateOfBirth] AS [dbo_PatientDateOfBirth0_1],[dbo_Patient].[Gender] AS [dbo_PatientGender0_2],[dbo_Patient].[PhoneNumber] AS [dbo_PatientPhoneNumber0_3]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Hierarchy 'PatientId - DateOfBirth - Gender - PhoneNumber' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
Processing Dimension 'Medicine Type' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
Processing Dimension Attribute 'Name' completed successfully. 4 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicineType].[Name] AS [dbo_MedicineTypeName0_0]
FROM [dbo].[MedicineType] AS [dbo_MedicineType]
Processing Dimension Attribute 'Medicine Type' completed successfully. 4 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicineType].[MedicineTypeCode] AS [dbo_MedicineTypeMedicineTypeCode0_0],[dbo_MedicineType].[Name] AS [dbo_MedicineTypeName0_1]
FROM [dbo].[MedicineType] AS [dbo_MedicineType]
Processing Dimension 'Medical Provider' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:32 AM; End time: 6/21/2006 9:41:32 AM; Duration: 0:00:00
Processing Dimension Attribute 'Name' completed successfully. 15 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[Name] AS [dbo_MedicalProviderName0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Gender' completed successfully. 3 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[Gender] AS [dbo_MedicalProviderGender0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Indicator Nurseor Physician' completed successfully. 4 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[IndicatorNurseorPhysician] AS [dbo_MedicalProviderIndicatorNurseorPhysician0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Physician Id' completed successfully. 9 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[PhysicianId] AS [dbo_MedicalProviderPhysicianId0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Nurse Id' completed successfully. 9 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[NurseId] AS [dbo_MedicalProviderNurseId0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Medical Provider' completed successfully. 15 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[MedicalProviderId] AS [dbo_MedicalProviderMedicalProviderId0_0],[dbo_MedicalProvider].[Name] AS [dbo_MedicalProviderName0_1],[dbo_MedicalProvider].[Gender] AS [dbo_MedicalProviderGender0_2],[dbo_MedicalProvider].[IndicatorNurseorPhysician] AS [dbo_MedicalProviderIndicatorNurseorPhysician0_3],[dbo_MedicalProvider].[PhysicianId] AS [dbo_MedicalProviderPhysicianId0_4],[dbo_MedicalProvider].[NurseId] AS [dbo_MedicalProviderNurseId0_5]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension 'Allergy' completed successfully.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
Processing Dimension Attribute 'Allergy Name' completed successfully. 6 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Allergy].[AllergyName] AS [dbo_AllergyAllergyName0_0]
FROM [dbo].[Allergy] AS [dbo_Allergy]
Processing Dimension Attribute 'Allergy' completed successfully. 6 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Allergy].[AllergyCode] AS [dbo_AllergyAllergyCode0_0],[dbo_Allergy].[AllergyName] AS [dbo_AllergyAllergyName0_1]
FROM [dbo].[Allergy] AS [dbo_Allergy]
Processing Dimension 'Return Visit' completed successfully.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:34 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
Processing Dimension Attribute 'Physician Id' completed successfully. 8 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[PhysicianId] AS [dbo_ReturnVisitPhysicianId0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Nurse Id' completed successfully. 9 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[NurseId] AS [dbo_ReturnVisitNurseId0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Time Start' completed successfully. 11 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[TimeStart] AS [dbo_ReturnVisitTimeStart0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Time End' completed successfully. 22 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[TimeEnd] AS [dbo_ReturnVisitTimeEnd0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Return Visit' completed successfully. 50 rows have been read.
Start time: 6/21/2006 9:41:33 AM; End time: 6/21/2006 9:41:33 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[PatientId] AS [dbo_ReturnVisitPatientId0_0],[dbo_ReturnVisit].[Date] AS [dbo_ReturnVisitDate0_1],[dbo_ReturnVisit].[PhysicianId] AS [dbo_ReturnVisitPhysicianId0_2],[dbo_ReturnVisit].[NurseId] AS [dbo_ReturnVisitNurseId0_3],[dbo_ReturnVisit].[TimeStart] AS [dbo_ReturnVisitTimeStart0_4],[dbo_ReturnVisit].[TimeEnd] AS [dbo_ReturnVisitTimeEnd0_5]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Cube 'MSCPROJECT_CUBE' completed successfully.
Start time: 6/21/2006 9:41:34 AM; End time: 6/21/2006 9:41:34 AM; Duration: 0:00:00
Processing Measure Group 'Prescription' completed successfully.
Start time: 6/21/2006 9:41:34 AM; End time: 6/21/2006 9:41:34 AM; Duration: 0:00:00
Processing Partition 'Prescription' completed successfully.
Start time: 6/21/2006 9:41:34 AM; End time: 6/21/2006 9:41:34 AM; Duration: 0:00:00
Processing Measure Group 'Patient Diagnosis' completed successfully.
Start time: 6/21/2006 9:41:34 AM; End time: 6/21/2006 9:41:34 AM; Duration: 0:00:00
Processing Partition 'Patient Diagnosis' completed successfully.
Start time: 6/21/2006 9:41:34 AM; End time: 6/21/2006 9:41:34 AM; Duration: 0:00:00
Errors and Warnings from Response
Errors in the high-level relational engine. The data source view does not contain a definition for the 'dbo_Prescription' table or view. The Source property may not have been set.
Errors in the OLAP storage engine: An error occurred while processing the 'Prescription' partition of the 'Prescription' measure group for the 'MSCPROJECT_CUBE' cube from the Analysis Services Project1 database.

Try in BI Dev studio go into partitions tab and try re-define source table for your 'Prescription' partition to come from data source directly, and not from DSV.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I will try it myself tomorrow morning though i do not know how to go about this but still i will give it a try.

Thanx otherwise.

errors in creating a cube by AMO

Hello

I am creating a cube in C# by AMO. I created data source view with two table and one relation, one table is dimension and another one is fact. Then I added a dimension to db. This dimesnion has two attribute one key and one regular. The key attribute has a AttributeRelationship to another attribute. After that I updated database and every thing is ok. Then I want to add cube. First I add it

Cube cube = db.Cubes.Add("MyCube");

cube.Dimensions.Add("T Deal");

cube.Validate(errors);

Still the errors.Count is 0. Then I create a MeasureGroup and add a Measure to that.

MeasureGroup measureGroup = cube.MeasureGroups.Add("Vw Cube Fact");

Microsoft.AnalysisServices.Measure measure = measureGroup.Measures.Add("Vw Cube Fact");

measure.Source = new DataItem("vw_CubeFact", "RES Notional");

measureGroup.Validate(errors);

but here there is an error as

No dimension relationships exist. The measure group 'Vw Cube Fact' is not related to any dimensions.

After that I try to add a dimension by

RegularMeasureGroupDimension rmgd = measureGroup.Dimensions.Add("T Deal");

rmgd.Validate(errors);

But the numbers of errors increased to two and the second one is

Granularity is not defined.

Can anybody please help me how can I fix it.

Thanks in advance

Mohsen

I did a mistake in setting AttributeRelationShips.

a very good reference is http://msdn2.microsoft.com/en-us/library/ms345091.aspx

Errorhandling in batches

Hi there,

I would like to know, if there is a way to handle errors in a batch without the batch being stopped? Take next example:

Two tabeles, SOURCE and DEST, both with columns ID as INT(primary key) and NAME as varchar(20).

When I do this: this statement will break with a duplicate key violation error when an existing

insert into DEST

select ID, Name

from SourceTable

This statement will break with a duplicate key violation error when an existing value is entered. Is there any way I can prevent this from happening? I am aware that with something like SELECT @.error = @.@.error and @.rowcount = @.@.rowcount I can find out what went wrong and locate the offending row. However, sofar I am not able to reset @.@.error to 0 again within the bacth.

Is this at all possible? I can make something like this with a CURSOR, but would like not to use this technique. Basically I want the offending records inserted into antother(error)table to deal with after the batch has completed.

TIA,

FreeHansje

First, select out the 'good' rows, and then the 'bad' rows.

INSERT INTO Dest
SELECT ID, Name

FROM SourceTable s

JOIN Dest d

ON s.ID <> d.ID

INSERT INTO DupIDs

SELECT ID, Name

FROM SourceTable s

JOIN Dest d

ON s.ID = d.ID

|||

Tnx for responding, Arnie.

This sure is a very good solution. This is however just an example, other errors may occur. I'd have to think up all possible errors and check the data for specifically those. I was hoping for a general way of isolating those error-records in the statement itself. But more and more I believe that's not possible.

Tnx again,

Errorhandling in batches

Hi there,

I would like to know, if there is a way to handle errors in a batch without the batch being stopped? Take next example:

Two tabeles, SOURCE and DEST, both with columns ID as INT(primary key) and NAME as varchar(20).

When I do this: this statement will break with a duplicate key violation error when an existing

insert into DEST

select ID, Name

from SourceTable

This statement will break with a duplicate key violation error when an existing value is entered. Is there any way I can prevent this from happening? I am aware that with something like SELECT @.error = @.@.error and @.rowcount = @.@.rowcount I can find out what went wrong and locate the offending row. However, sofar I am not able to reset @.@.error to 0 again within the bacth.

Is this at all possible? I can make something like this with a CURSOR, but would like not to use this technique. Basically I want the offending records inserted into antother(error)table to deal with after the batch has completed.

TIA,

FreeHansje

First, select out the 'good' rows, and then the 'bad' rows.

INSERT INTO Dest
SELECT ID, Name

FROM SourceTable s

JOIN Dest d

ON s.ID <> d.ID

INSERT INTO DupIDs

SELECT ID, Name

FROM SourceTable s

JOIN Dest d

ON s.ID = d.ID

|||

Tnx for responding, Arnie.

This sure is a very good solution. This is however just an example, other errors may occur. I'd have to think up all possible errors and check the data for specifically those. I was hoping for a general way of isolating those error-records in the statement itself. But more and more I believe that's not possible.

Tnx again,

Error-Data Conversion Component

Hi

I am getting error in an SSIS,

Source-Excel File

Destination-SQL Server

Source has fields of DataType-Unicode strig[DT_WSTR], I am using "Data Conversion" Component to convert these into Integer.

Source(Unicode strig[DT_WSTR])Destination(numeric[DT_NUMERIC] Precision:18 ; Scale:0)

Error:

[Data Conversion [14]] Error: Data conversion failed while converting column "Column1" (99) to column "Column1_Converted" (204). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [14]] Error: The "output column "Column1_Converted" (204)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Column1_Converted" (204)" specifies failure on error. An error occurred on the specified object of the specified component.

Can anyone tell me which Data Type should I use to convert string into integer.

Thanks

If you have control over the database table, you should get rid of using a numeric data type to hold an integer. Instead, you should use an integer data type.

Then inside SSIS, I'd personally use a derived column instead of a data conversion component and use the following expression:

(DT_I4)TRIM([yourField])

You can replace DT_I4 with any of the appropriate integer data types. (unsigned integers, 8 byte integers, etc...)|||

I can't change the DB table as an application is running on that and we are using this SSIS for the initial load of the data. But I am trying what u have suggested. Thanks a lot for replying, I'll get back to u after trying it.

|||In that case, this is a better expression:

(DT_NUMERIC,18,0)TRIM([yourColumn])|||

I tried it, but still no luck

This is what I am doing:

Step 1:Excel Source->

Step 2: columns-->

Derived Column Name Derived Column Expression Data Type Length

Column1 Replace 'Column1' (DT_I4)TRIM([Column1]) Unicode string [DT+WSTR] 255

Column2 Replace 'Column2 (DT_I4)TRIM([Column2) Unicode string [DT+WSTR] 255

Step 3ata Conversion-->

Input Column Output Alias Data Type Length Code Page

Column1 Column1_STR eight-byte unsigned integer[DT_UI8] 1252 (ANSI -Latin I)

Column2 Column2_STR eight-byte unsigned integer[DT_UI8] 1252 (ANSI -Latin I)

Step 4: OLE DB DEstination

With Columns mapped to Column1_STR and Column2_STR

Error:

[Derived Column [20]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [20]] Error: The "component "Derived Column" (20)"
failed because error code 0xC0049064 occurred, and the error row
disposition on "input column "Column1" (1260)" specifies failure on error.
An error occurred on the specified object of the specified component.

|||

I tried (DT_NUMERIC,18,0)TRIM([Column1]) also,

Error

[Derived Column [20]] Error: The "component "Derived Column" (20)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Column2" (1260)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (20) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component,
but the error is fatal and will cause the Data Flow task to stop running.

I can change the Excel file if it can solve this problem.

|||Excel Source -> Derived Column -> OLE DB Destination

Derived column component:
Derived Column Name Derived Column Expression Data Type
NumColumn1 <add as new column> (DT_NUMERIC,18,0)TRIM(Column1) numeric [DT_NUMERIC]

Then just use NumColumn1,2,3, etc... in your destination. Don't use the original columns from the Excel source. You can't cast a column to a different data type in place -- you need to create a new output column.|||

Still no luck,

Now I am adding a new column,

Derived Column Name Derived Column Expression Data Type Precision Scale
NumColumn1 <add as new column> (DT_NUMERIC,18,0)TRIM([Column1]) numeric [DT_NUMERIC] 18 0

NumColumn2 <add as new column> (DT_NUMERIC,18,0)TRIM([Column2]) numeric [DT_NUMERIC] 18 0

Error:

Derived Column [20]] Error: The "component "Derived Column" (20)" failed because error code 0xC0049064 occurred, and the error row disposition on
"output column "NumColumn1" (143)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (20) failed with error code 0xC0209029. The identified component returned an error
from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

|||Are you sure you have numeric (and nothing else) in those columns? I'm expecting to see some alpha characters or something in your columns that can't convert. Do you have decimal points in the raw data? Better yet, can you post some sample data from column1 and column2?|||

Column1 Column2
Row1- 21 34
Row2-
Row3- 28 246
Row4-
Row5- 21 223
Row6-
Row7-
Row8- 30 206
Row9- 21 145
Row10-
Row11- 25 236

|||New expression:

ISNULL(TRIM([yourColumn])) || TRIM([yourColumn] == "" ? NULL(DT_NUMERIC,18,0) : (DT_NUMERIC,18,0)TRIM([yourColumn])
|||

Added new Expression

error:

[Derived Column [20]] Error: The "component "Derived Column" (20)" failed because
error code 0xC0049063 occurred, and the error row disposition on
"output column "Derived Column 1" (1236)" specifies failure on error.
An error occurred on the specified object of the specified component.

|||

I think you will need some extra parens to get what you want (both the isnull test and the empty string test as the condition):

( ISNULL([yourColumn]) || TRIM([yourColumn]) == "" ) ? NULL(DT_NUMERIC,18,0) : (DT_NUMERIC,18,0)TRIM([yourColumn])

The error you saw, though, was that the conditional operation failed, so this might not fix that still.

|||

Yah u're right, I've added the extra parenth but still no luck.

Error{no row is was updated}

hey expert out ther..

i have encounter this error ..and i dont know what is the wrong with it..

the error goes:

No row was updated

Error source: .net SQLClient Data provider

Error Message:string or binary data would be trancated.

plz help me to slove this prob.

thanks in advance!!!!!!!

Could you please provide more information, code sample causing this error?

Thanks

|||

Ehm.......there was no code thats cause this error...

It was ok when i just add data in two rows...but once i input more than 2 rows..

this error will occur!!!!!

|||

Hi

I guess you mean, that you see this error, when you put in data in tht sql management programm to the opended database table.

I have the same errormessage. There are at about 8 tables and different relations, quite a small database. When I'm trying to change table properties and klick the save button the wizard shows me the other affected tables and tells me, that there could be errors with the rowguid column. And of course there are problems and changes to the table properties aren't able.

Besides I think, that also this rowguid column has something to do with the errormessage we get, because when entering a new row, no "uniqueidentifier" is generated in the specified column of rowguid.

I hope that now someone could help us.

best regards

Hannes

Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

The only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc

Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

The only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc