One data source view and multiple data source

Hi,

In my datawarehouse we have different database one for dimensions and one for fact tables.

can we create a cube to pull dimensions from one data soure and fact from other databsource?

I recommend you to have the fact tables and the dimensions in the same database.

Your long term quick-fix is to use views between the databases.

Your short scenario description looks like you are building a cube directly from a source system.

If you need to connect another source system you will have to create a data wareouse to consolidate each source.

If not, you wille be creating information silos above each source system that you cannot connect to a second system.

HTH

Thomas Ivarsson

|||both the source are on the same SQL Server but different databases, I was planning to use View but was just considering the performance impact that will cause.|||

Actually Analysis Services allows for having dimensions and parittions to come from different datasources.

The caveat here is not to use different datasources to define your dimension. In such case Analysis Services might decide to use OPENROWSET clause as part of the query it sends during processing of dimension. This would slow you down considerably. But having partitions to come from different datasource should be perfectly fine.

Run Profier to capture SQL queries Aanlysis Server sends during processing and verify you dont get OPENROWSET is these queries.

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

One column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql

OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

Hello,

Can I import an OLTP (Reltional DB) as a Data Source into SQL Server
Analysis Services 2005 and then use the Cube Wizard and the new Data
Source View feature to create the OLAP model ?

Or do I have to first design an OLAP Data Warehouse with a Star Schema
and then import this DW as a Data Source into my Analysis Services
Project.

With SQL Server 2000 , OLAP would be the way to go..but with SQL
Server 2005 , it seems as though the wizard and data source view
features do half the work for you.

I have an OLTP DB and am not sure which route I should take ! Any
suggestions / input would be much appreciated.

Thanks in Advance...

Regards
RusszeeHere's the followup answer to my own post...

Just what I thought...It's the new UDM all the way in SQL Server
2005 !!!

"SQL Server 2005 Analysis Services can create UDM cubes without the
intermediate step of building a star schema data warehouse. The UDM
enables organizations to build reporting applications directly against
a production system and doesn't require an intermediate data warehouse
as in the past. This is because the UDM doesn't require the data to be
in a star or snowflake schema, but can connect to any data source
whose data is stored in a third normal form. "

Source : http://www.microsoft.com/technet/pr...5/solvngbp.mspx

OLEDB Source to Flat File

Hi,

I'm using an OLEDBSource to select some data and then putting to in a Flat File destination.

However, when I look at the data in the OLEDBSource, they′re like this:

1. id

2. name

3. address

...but in the flatfile it comes out in the wrong order.

How can I fix this?

Thank you so much.

Create the columns in the flat file connection manager in the order you need them to be in.

OLEDB Source Table Locks?

Hi All,

Is it possible that an OLEDB Data Flow Source is imposing locks on the source tables? The source is an SQL Server OLTP environment, and although the package will be scheduled to run nightly when the application sees little to no use, I want to be sure that the process isn't impacting any application functions.

Thanks for the advice!

Rocco

An OLE-DB source will impose the same locks as if you were running the SELECT satement yourself from any other tool, so normaly you would expect some shared locks to allow you to consistently read the data requested.

Using Profiler will show you details of the SQL statement, and you can also choose to show locks details in profier, just filter for the SSIS connection/machine/user/application to focus on SSIS generated information as opposed to regular application traffic.

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.sql

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.

OleDb Source Error - Connecting to Oracle 9i

Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:

Warning at "guid code": Cannot retreive the column code page info from the oledb provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale Id will be used.

Could anyone please help me trouble shoot this problem.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

Hi,

Click on the OLE DB Source component and check for the properties, you have to keep the property "Alwaysusedefaultcodepage" to true.

Hope this will help you.

Regards,

Raju

|||

Hi Raju,
Thank you very much for your help.
Well, I was not aware of this option and it helped me a lot to finish my work.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

Oledb Source adapter exception Error code: 0x80040E21

Hi

I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)

Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.

Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error

TITLE: Microsoft Visual Studio

Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION:

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

BUTTONS:

OK

I am not able to figure out how to resolve this.

I am receiving the exact same error. Can't run custom queries against the database and it is frustrating. Someone from Microsoft needs to respond to this immediately...it's not acceptable.sql

Oledb Source adapter exception Error code: 0x80040E21

Hi

I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)

Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.

Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error

TITLE: Microsoft Visual Studio

Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION:

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

BUTTONS:

OK

I am not able to figure out how to resolve this.

I am receiving the exact same error. Can't run custom queries against the database and it is frustrating. Someone from Microsoft needs to respond to this immediately...it's not acceptable.

OLEDB source - Use table or select only columns needed.

Hi All,

With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?

I cannot see any difference in performance between the two.

Thanks.

Always always always use a SQL command so as to avoid the situation documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx I can't stress this enough.

Also check #4 here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx Basically, only pull in the data that you need otherwise performance will suffer.

-Jamie

|||Thanks Jamie but...

I would still like to know the underlying reason it is bad :)

Accepted that Select * is bad due to many reasons but:
My "tables" are views which themselves only select columns required for the data flow and nothing more.

Basically I am being lazy - I write the columns out in the view and don't _really_ want to write them again in SSIS :)

From a performance point of view, the above method is exactly the same either way. I cannot and have not seen what you described.

Will play around some more and try find a reason (unless someone wants to save us the trouble....)|||

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

|||

Jamie Thomson wrote:

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

A counter to that is I hate seeing any form of SQL in SSIS. Rather have the logic in a view / proc or just pull from the table. Make life easier when looking for bugs.
(yes, you could make a rule such as "do not use anything more that Select *" :)|||

Let's agree to disagree! :)

-J

OLEDB Provider for SQL Server Errors

Hi Yosh,
I saw a number of posting relating to the Jet database but I am not using
the Jet database in any way do I doubt that is the source. This app has
been installed on 20 Windows XP PCs for the last six months and the today is
the first time we have seen this happen. It is also occuring on multiple
PCs.
Thanks for your help,
Darrel
"Yosh" <yoshi@.nospam.com> wrote in message
news:Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Actually it sounds more like a JET error. Download and install the latest
> JET 4.0.
> That should fix the problem. It sounds like a JET 3.5 problem.
> Yosh
>Hello Darrel,
It seems to be a timeout issue. For example:
-3633 - Microsoft OLE DB Provider for SQL Server: Timeout expired
Error : 3633 : Microsoft OLEDB provider for ODBC drivers : Oracle ODBC ORA
ORA-01013 : User requested cancel of the current operation.
It is usually caused by performance or network issue. You could set
CommandTimeout of ADODB.command in client application to 0 if you never
want a command to timeout.
Also, it could be a driver related issue though it does not seem to be true
for this specific case.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Darrel Miller" <darrel@.tavis.ca>
| References: <OQo8uXrwFHA.2228@.TK2MSFTNGP11.phx.gbl>
<#h6aRbrwFHA.612@.TK2MSFTNGP10.phx.gbl>
<#4wDOoswFHA.2312@.TK2MSFTNGP14.phx.gbl>
<eGkZa0twFHA.1996@.TK2MSFTNGP10.phx.gbl>
<Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: OLEDB Provider for SQL Server Errors
| Date: Mon, 26 Sep 2005 20:27:49 -0400
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: toronto-hse-ppp3944778.sympatico.ca 70.49.61.98
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:120894
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Yosh,
|
| I saw a number of posting relating to the Jet database but I am not using
| the Jet database in any way do I doubt that is the source. This app has
| been installed on 20 Windows XP PCs for the last six months and the today
is
| the first time we have seen this happen. It is also occuring on multiple
| PCs.
|
| Thanks for your help,
|
| Darrel
|
| "Yosh" <yoshi@.nospam.com> wrote in message
| news:Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl...
| > Actually it sounds more like a JET error. Download and install the
latest
| > JET 4.0.
| >
| > That should fix the problem. It sounds like a JET 3.5 problem.
| >
| > Yosh
| >
|
|
||||You may want to check the SQL Server "Maximum concurrent user connections"
value under the SQL Server Properties Connection tab.
This variable states the following:
"Specify the maximum concurrent user connections. Entering zero means there
can be an unlimited number of concurrent user connections."
If the number of users trying to connect to the SQL Server instance exceeds
this value, you will have experience sporadic errors like the one you've
described. This includes the connections your application makes along with
all the other applications connecting to the SQL Server instance. Also,
think about all the developers or users using the query analyzer. Each query
window is a seperate connection.
One thing developers do is open multiple connections within their
application. If your application opens 3 connections and you have 20 users,
that will be 60 SQL Server connections.
Yosh
"Darrel Miller" <darrel@.tavis.ca> wrote in message
news:enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl...
> Hi Yosh,
> I saw a number of posting relating to the Jet database but I am not using
> the Jet database in any way do I doubt that is the source. This app has
> been installed on 20 Windows XP PCs for the last six months and the today
> is the first time we have seen this happen. It is also occuring on
> multiple PCs.
> Thanks for your help,
> Darrel
> "Yosh" <yoshi@.nospam.com> wrote in message
> news:Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hi Peter,
Thanks for the info. I was starting to expect it to be a timeout problem.
Now I just have to find out what is causing it. I don't suppose you can
point me to a place where I can see a list of the errors that the Microsoft
OLE DB Provider for SQL Server can generate? I would like to set traps for
these errors so that I can handle them properly.
Thanks,
Darrel
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:Ty7XIMxwFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello Darrel,
> It seems to be a timeout issue. For example:
> -3633 - Microsoft OLE DB Provider for SQL Server: Timeout expired
> Error : 3633 : Microsoft OLEDB provider for ODBC drivers : Oracle ODBC ORA
> ORA-01013 : User requested cancel of the current operation.
> It is usually caused by performance or network issue. You could set
> CommandTimeout of ADODB.command in client application to 0 if you never
> want a command to timeout.
>
> Also, it could be a driver related issue though it does not seem to be
> true
> for this specific case.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>|||Hi Yosh,
On the recommendation of another forum member I checked the max connections
property and it is set to zero.
Thanks for the suggestion,
Darrel
"Yosh" <yoshi@.nospam.com> wrote in message
news:uvhYDkxwFHA.3904@.TK2MSFTNGP10.phx.gbl...
> You may want to check the SQL Server "Maximum concurrent user connections"
> value under the SQL Server Properties Connection tab.
> This variable states the following:
> "Specify the maximum concurrent user connections. Entering zero means
> there can be an unlimited number of concurrent user connections."
> If the number of users trying to connect to the SQL Server instance
> exceeds this value, you will have experience sporadic errors like the one
> you've described. This includes the connections your application makes
> along with all the other applications connecting to the SQL Server
> instance. Also, think about all the developers or users using the query
> analyzer. Each query window is a seperate connection.
> One thing developers do is open multiple connections within their
> application. If your application opens 3 connections and you have 20
> users, that will be 60 SQL Server connections.
> Yosh
>
> "Darrel Miller" <darrel@.tavis.ca> wrote in message
> news:enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl...
>|||Hello Darrel,
Sorry there are no document on this error at present, and your feedback on
this is routed to the proper channel. Also, if you want to trace the error
via live debug or user dump, we recommend that you contact Microsoft
Product Support Services and open a support incident and work with a
dedicated Support Professional.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Darrel Miller" <darrel@.tavis.ca>
| References: <OQo8uXrwFHA.2228@.TK2MSFTNGP11.phx.gbl>
<#h6aRbrwFHA.612@.TK2MSFTNGP10.phx.gbl>
<#4wDOoswFHA.2312@.TK2MSFTNGP14.phx.gbl>
<eGkZa0twFHA.1996@.TK2MSFTNGP10.phx.gbl>
<Opnsn6twFHA.2072@.TK2MSFTNGP14.phx.gbl>
<enHbMpvwFHA.3756@.tk2msftngp13.phx.gbl>
<Ty7XIMxwFHA.768@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: OLEDB Provider for SQL Server Errors
| Date: Tue, 27 Sep 2005 01:34:48 -0400
| Lines: 41
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#MwkvUywFHA.2960@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: locweld.com 69.70.2.194
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:120918
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Peter,
|
| Thanks for the info. I was starting to expect it to be a timeout
problem.
| Now I just have to find out what is causing it. I don't suppose you can
| point me to a place where I can see a list of the errors that the
Microsoft
| OLE DB Provider for SQL Server can generate? I would like to set traps
for
| these errors so that I can handle them properly.
|
| Thanks,
|
| Darrel
|
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:Ty7XIMxwFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > Hello Darrel,
| >
| > It seems to be a timeout issue. For example:
| >
| > -3633 - Microsoft OLE DB Provider for SQL Server: Timeout expired
| >
| > Error : 3633 : Microsoft OLEDB provider for ODBC drivers : Oracle ODBC
ORA
| > ORA-01013 : User requested cancel of the current operation.
| >
| > It is usually caused by performance or network issue. You could set
| > CommandTimeout of ADODB.command in client application to 0 if you never
| > want a command to timeout.
| >
| >
| > Also, it could be a driver related issue though it does not seem to be
| > true
| > for this specific case.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
|
|
|

OleDB Connection to Excel

Hi,

My application connects to excel files.
I am using this code:

dc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=YES;\"");

It works fine on Excel 2000 on all computers. Also it works on some computers with Excel 2002/XP, but on some computers with Excel 2002/XP it doesn't work.

How can I solve it?

Thank's
AlexeiHi,
I used above code sample for my program. but i did not worked.can u help me. I want to do ,take excel information to one record set or data set.I can't understand how to do it.I send u my connection coding.please check it send me wht is the correct syntax or correct code and tell me wht is the componts want. this program using the .net
following has my codes:

MyConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=D:\Oven Sheet (Track)\Data For Symix_23082005-Tracks.xls;Extended Properties=\Excel 8.0")

|||Are you sure you have the right version of Jet on the machine? Check this by right clicking on the desktop and create a new text file, call it demo.udl. This is a datalink file. If you double click on the file it should show you a list of OLE DB Providers, check that Jet 4.0 is installed.

If it is installed then try changing \Excel 8.0 to plain Excel 8.0

OLEDB AS400 Pipeline threads

A simple dataflow :

Data source OLEDB AS400 : - data access sql command from variable

SELECT 'DEN' AS "Company","TDEN.IHD".* FROM "TDEN.IHD" WHERE (ORDNI1 > 48960)

Data destination : OLEDB Sql Server File

- No Problem when Data Source is executed As Sql command

Purpose :

Load (new) data from AS400-files and load them in the corresponding sql server table. Therefore each day the maximum ordernumber in the sql database is searched And given to a script which makes an sql command user::SqlSelect.

All This this to prevent loading each day all records again.

I choose to make the data access by an variable because I have more then one company with the same formatted data files. e.g.

Company XXX has a file named FXXX.AAA

Company YYY " FYYY.AAA and so on ....

All data for all companies has to be imported in one sql server tabel. Ofcourse with a company field.

- ODBC Does not allow sql command from variable

Errors
[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. 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.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. 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.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: 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.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Is there anyone who had the same problems ?

By the way the sql command from variable is double checked a thousand times :)

Thanks

Ronny

0x80040E00 is not an SSIS error code. Instead it is most likely coming from the provider so you should see if the AS400 OLEDB provider you are using lists this error and what it means.

Matt

|||

Hello Matt,

This could be , but why was it then working under Sql server 2000 ? Also the ODBC connections in DTS200 were far more ?ntelligent".

regards

Ronny

OLEDB Access PB

Hi

I definite a source OLEDB MS Access and my fields of the type “Text” are seen in type of field DT_WSTR (Unicode) instead of DT_STR.

I do not include/understand why? and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution?

thank for your solution

You can use a Derived Column transform to convert between Unicode and Ansi. Use the operators under the Type Casts section.

Code Snippet

(DT_STR, 50, 1252) [Your_Column]

|||

Hi plab,

You may even use a DataConversion transformation to change type DT_WSTR to DT_STR with a required length.

Thanks

Subhash Subramanyam

OLE DB Source using table name variable

I have a package-level variable [User::viewName], type = string, containing a view name. I want to setup an OLE DB source to use this variable value as the source, so Data access mode = "Table name or view name variable". The Variable name dropdown contains [User:viewName], so I select it. When I click OK to leave the edit dialog I get the error:

The variable User::viewName is required to be of type "VT_BSTR".

Only variables of type String occur in the Variable name dropdown; if I try changing it to a type other than string it doesn't occur in the dropdown. What is VT_BSTR and how can I change the variable type to it?

I've never seen VT_BSTR in my life, that isn't a data type that I know of. Something is awry here - I wonder if it could be package corruption. Does the same happen when you try a differrent string variable?

-Jamie

|||I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?|||

I don't know of any - but it does sound as though some corruption has occurred somewhere. Can you share the contents of the .dtsx file? (i.e. open it in notepad and copy the contents to here)?

-Jamie

|||

mruniqueid wrote:

I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?

Can't think it matters, but be sure you generate a new GUID on the copied package. Control-flow background: right click, properties. Select the drop down in the ID field and generate a new GUID.|||

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from? I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

|||

mruniqueid wrote:

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from?

The name of the table needs to be stored in the variable.

mruniqueid wrote:

I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

1. Create the variable of type string

2. Add the table name into teh variable

3. Create your OLE DB Source and select the variable that you have just chosen.

-Jamie

|||

I didn't realize the variable value was evaluated at design time, since it's set at runtime. Makes sense though - thanks a lot!

- Dana

ole db source query assistance requested

my query is below:

"SELECT * FROM " + @.[User:: TableName] +
" WHERE OrderDate = " + "'" + @.[User::dTrxnDate] + "'"

needless to say, the ole db source editor is giving me syntax errors. the data type of TableName is string. the data type of dTrxnDate is DateTime.

can someone please help me resolve the syntax errors?

thanks in advance.

"SELECT Column FROM " + @.[User::TableName] + "
WHERE DateFilterColumn = '" +
(DT_WSTR,4)YEAR(@.[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@.[User::DateTimeVar]), 2)
+ "'"

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

Expression Samples
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html)

|||

DarrenSQLIS wrote:

"SELECT Column FROM " + @.[User::TableName] + "
WHERE DateFilterColumn = '" +
(DT_WSTR,4)YEAR(@.[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@.[User::DateTimeVar]), 2)
+ "'"

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

Expression Samples
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html)

thanks darren, but i'm still getting a syntax error. my query is below:

"SELECT * FROM " + @.[User::TableName] +
" WHERE OrderDate = '" + (DT_WSTR,4)YEAR(@.[User::dTrxnDate]) + RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::dTrxnDate]), 2) + RIGHT("0" + (DT_WSTR,2)DAY(@.[User::dTrxnDate]), 2) + "'"

below is the syntax error:

TITLE: Microsoft Visual Studio

Error at Load FactOrders Data Flow [Source from xtract [7629]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Incorrect syntax near '+'.".

ADDITIONAL INFORMATION:

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

BUTTONS:

OK|||

That expression works for me, result is -

SELECT * FROM MyTable WHERE OrderDate = '20060915'

Are you sure you have set the full expression in the variable? Have you used the expression builder, which only became available for the variable Expression property with SP1. Previously pasting a multi-line expression into the single line property grid box did not work, you just got the first line.

What does your expression evaluate to in the variable's expression ?

|||

DarrenSQLIS wrote:

That expression works for me, result is -

SELECT * FROM MyTable WHERE OrderDate = '20060915'

Are you sure you have set the full expression in the variable?

i'm evaluating the expression within the ole db source editor. should i evaluate the expression within a variable instead?

my understanding is that the ole db source can evaluate ssis expressions. is this not true?

|||i'm still seeking assistance.|||

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

|||

Rafael Salas wrote:

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

that's what i thought, but i wasn't sure.

thanks.

|||

Rafael Salas wrote:

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

that's what i thought, but i wasn't sure.

thanks.

OLE DB Source parameters ignored when subquery?

I am using an OLE DB Source with SQL command for the data access mode. I defined parameters, then added a complex query with subqueries. It seems like the parameters for a query are not being filled correctly (because I am getting too many rows returned). At one point I saw an error message the said something to the effect that the parameters would be ignored when there was a subquery in the SQL.

Can anyone shed some light on this?

Thanks,

Laurence

I don't know if that is true or not but regardless of that you should stop using parameters as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Jamie,

I have read your blog many times and it is really helpful. Thanks.

In this instance, the query is long and complex, and I might as well go to a script task as a string variable.

I am disappointed that parameters do not seem to be working correctly, thereby making my life more difficult.

Laurence

|||

Yeah, script task is a good option in those circumstances.

N.B. SP1 will have an expression builder attached to variables which will make it easier to build your expressions.

-Jamie

|||Would you mind sharing the long, complex query that didnt work for you?

OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

ole db source editor "preview" throwing an error

i have a data flow configured to use a ole db source. the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection. the data access mode of the ole db source is "sql command". below is the sql command text:

SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?

this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:

Parameter0: User::ExtractStartDate

Parameter1: User::ExtractStopDate

ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004. however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".

as far as i can tell, i have the ole db source configured correctly. thus, i can't figure out why this error is being generated. has anyone else experienced this issue? if so, were you able to resolve it? is this a bug?

thanks in advance.

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

|||

Brian Knight wrote:

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

thanks for the reply. i wonder if this is a bug or a "feature".|||

Not sure it's really either. It was the same way in DTS. Essentially the provider can't parse the literal query. Probably the ultimate answer is to perform the preview and parse like Reporting Services does where it pops open a box asking for the variable values you'd like to parse. As it stands, SSIS or DTS doesn't have enough info to perform a parse.

Brian