Problems importing data from an ODBC source

I'm having an odd issue importing data from an ODBC source using a DataReader Source.

There's data in the source table, and if I use a non-SSIS tool to query the source, all the data is visible. However, if I use the SSIS DataReader source, It returns no rows, and throws no error.

Any ideas for figuring this one out? This is affecting about 10% of the tables from this data source, and it's our ERP application, so there's limits on what I can do to the source schemas.

Thanks!

What types of colums are contained in these tables?

Problems Creating a Cube using Informix IDS 10.0 as Data Source

Greetings:

I am a n00b trying to create a new SQL Server 2000 Analysis Services Cube using Informix IDS 10.0 as the data source. The trick is that the raw data needs to stay in IDS -- no ETL to get the data into SQL Server.

I have installed the Informix client stuff on my SQL Server computer, but when I try to use the "normal" way to create a cube, I am having problems. Here is what I am doing:

1. Right-click "Data Sources" folder under the new AS database I just created -- select "New Data Source".
2. Select "Informix OLE DB Provider".
3. Type in connection info, and click "Test Connection". The test succeeds!!!!!
4. Save it.
5. Right-click "Cubes" folder under the new AS database -- select "New Cube", then "Wizard".
6. When I am supposed to see a list of tables from my newly created data source, I see just an error that says AS cannot connect to IDS.

I am confused by the fact that AS is apparently either using different info or a different method to connect to IDS than what it used when I clicked "Test Connection" when I created the data source.

Hopefully, someone has some past experience getting these 2 products to "play nice".

Any assistance would be greatly appreciated.

-Joe

Joe, did you have any luck resolving this? I'm attempting to do the same and just getting started. Thanks!

Problems Creating a Cube using Informix IDS 10.0 as Data Source

Greetings:

I am a n00b trying to create a new SQL Server 2000 Analysis Services Cube using Informix IDS 10.0 as the data source. The trick is that the raw data needs to stay in IDS -- no ETL to get the data into SQL Server.

I have installed the Informix client stuff on my SQL Server computer, but when I try to use the "normal" way to create a cube, I am having problems. Here is what I am doing:

1. Right-click "Data Sources" folder under the new AS database I just created -- select "New Data Source".
2. Select "Informix OLE DB Provider".
3. Type in connection info, and click "Test Connection". The test succeeds!!!!!
4. Save it.
5. Right-click "Cubes" folder under the new AS database -- select "New Cube", then "Wizard".
6. When I am supposed to see a list of tables from my newly created data source, I see just an error that says AS cannot connect to IDS.

I am confused by the fact that AS is apparently either using different info or a different method to connect to IDS than what it used when I clicked "Test Connection" when I created the data source.

Hopefully, someone has some past experience getting these 2 products to "play nice".

Any assistance would be greatly appreciated.

-Joe

Joe, did you have any luck resolving this? I'm attempting to do the same and just getting started. Thanks!sql

Problems connecting to SQL server(data source)

Hi,

I am new to SQL server and am experiencing problems with connecting to SQL server express from my VS C++ 6. Since its just the start, I am trying to play around with data access on the computer with the server installed.

I want to connect to the server through ODBC and understand that I can do so by first creating a data source. However, at the Data Source Administrator, there were errors when connecting to the server. I tried with different server names, with Shared memory/TCP/Named pipes enabled(in all combinations) at the Configuration Manager but to no avail.

However, I am able to contact to the server through Shared memory at the Management Studio Express. I don't know what is wrong.

Let me know if you need extra information to give me assistance.

Thanks in advance.

Mel

The most likely issue is that you haven't applied the SQLEXPRESS instance name to your server on the first page.

Name: <any name>

Description: <any description>

You could use either of the following for your server name:

Server: <server name>\SQLEXPRESS

Server: .\SQLEXPRESS

Server: (local)\SQLEXPRESS

I think you can bypass creating a data source in ODBC by specifying the information directly in the connection string,

a simple connection string might look like "DRIVER={SQL Native Client}; SERVER=(local)\SQLEXPRESS; Trusted_Connection=YES". This link might offer a little bit of help: http://doc.ddart.net/mssql/sql70/6_odbcht_6.htm You should also check out the SQL Server 2005 samples, they have a fairly decent ODBC section. You can download them here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Hope this helps!

John

sql

Problems bringing up Project REAL

Most of the installation went well. I got to the point of attaching the source database, but had problems attching the warehouse database:
Database 'REAL_Warehouse_Sample_V6' cannot be started in this edition of SQL Server because it contains a partition function 'pf_Range_Fact'. Only Enterprise edition of SQL Server supports partitioning.
I am running standard edition at mey development workstation, the only one we have running. I do not necessarily need to see partitioning working. But sounds like I will need to install Enterprise edition at my development computer to go down this path of discovery? Anyone been able to do otherwise?
Is there any way to get in and look at the stored procedures at least, or schemas?
Even after going back, it was not clear to me from the prerequisit list that Enterprise edition was required for this.Developer Edition would work as well. Simply a $50 fee for that.|||Yes, it works!

Edition upgrade sucks. I've done it twice, two different ways, and each time it trashes SQL installation. Both times I ended up doing a complete uninstall, and then install. So really the edition upgrade was a wasted diversion of time.

Problems acessing the database after creating a website on localhost

I cannot access the data source when I create a website in express - when I try to connect to the database I get an error telling that I am unable to connect to the data source - I tried doing it through the Visual Studio and through the Web Configaration.

Can some please help - I need to conncet!!!

What the connction string and the exact error message ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

"Failed to generate a user instance of SQL Server due to failure in starting the process for the user instance. The connection will be closed"

This is the error messgae that I get when I try to connect to the data source.

Can you help?

|||Do you have SQL Server Express installed ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Reese C. M. Harris wrote:

"Failed to generate a user instance of SQL Server due to failure in starting the process for the user instance. The connection will be closed"

This is the error messgae that I get when I try to connect to the data source.

Can you help?

This is a known issue and the solution is the one posted by user Reen in the long thread below. Hope this helps.

http://forums.microsoft.com/msdn/showpost.aspx?postid=293667&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

Problem: Reporting using Report Model

I'm having a reporting problem when using the report model as my data source. I was able to duplicate the problem by trying to create a report dataset using the ModelTutorial (based on the AdventureWorks database) as my data source.

For example, say you wanted to get all products which have illustrations. The query shown below would retrieve the 23 rows as expected (don't worry about duplicate products for this example).

SELECT P.ProductID, P.ProductNumber, P.Name, I.IllustrationID, I.ModifiedDate
FROM Production.Product P
INNER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID
INNER JOIN Production.ProductModelIllustration PMI ON PM.ProductModelID = PMI.ProductModelID
INNER JOIN Production.Illustration I ON PMI.IllustrationID = I.IllustrationID

Trying to create a dataset to get this same information using the report model as the data source is my problem. I am unable to get fields from all the following 4 tables: Product, ProductModel, ProductModelIllustration, and Illustration. I can get fields from the ProductModel, ProductModelIllustration, and Illustration tables, but not the Product table. The circle with the diagonal shows up when I try to drag a field from the Product table into the dataset area. What am I doing wrong?

Are you able to query Product table alone?|||

Yes, I can return records from the Product table alone. I created a report to display the product name and number for all products. It returned 504 rows as expected.

In addition, I can generate a dataset with fields from both the Product and ProductModel table. However, when I try to add a field from the ProductModelIllustration table, I am denied (circle with the diaganol line).

|||It is hard to guess without knowing your model.|||

As mentioned above, I duplicated the problem with the ModelTutorial Model (based on the AdventureWorks database). You can duplicate this model by following the "Creating a Report Model" tutorial in SQL Server Books Online (see below link).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/395a4f26-6401-4ee1-8658-be8b43cc70a8.htm

If the link above does not work for you, you can find the tutorial in books online by searching for tutorials [Reporting Services], then click on Reporting Services Tutorials link, then click on the Creating a Report Model link. Once you have the model built and deployed, you should have no problem duplicating my problem.

|||

My understaning is that relations are:

Product -(many to one)- ProductModel -(one to many)-Illustration

This case is not supported automatically.

|||

You are close, but the relations are as follows:

Product -(many to one)- ProductModel -(one to many)- ProductModelIllustration -(many to one)- Illustration

This scenario will come up frequently for report model designers. In fact, it will occur every time there is a junction (aka intersection) table in the model.

If this case is not supported automatically, can you explain in detail the best way to handle it manually (e.g. view in database, new named query in model, etc.)?

Thanks for your help.

|||

Transition many-one-many is not supported. Additional relation (in this case ProductModelllustration -Illustration) is not important.

Easiest way to do it is to add named query.

Problem with XML Source

Hello everyone we are having a problem I presume many of you have already dealt with.
We are using the XML source dataflow component, to parse xml-documents exported by an external system.

The problem that arise is when som values from the from the external system i "Null" that element is excluded from the XML file.

Ex.

Note: In the case below the second post has phone==NULL in database and we get the following XML.

<indata>
<post>
<name>TheName1</name>
<phone>123456789</phone>
</post>
<post>

<name>TheName2</name>
</post>

</indata>

This cause the XML source to throw an "Error Disposition" - Error.

Is there a solution to this problem?

You need to modify the XSD Schema to allow <phone> to be an optional element.

<xs:element minOccurs="0" name="phone" type="xs:string" />

problem with xml data source

hi,

i have a problem:

i got a report to which i pass a xml string like

<xml>

<somedata>

<somemoredata>

value

</somemoredata>

</somedata>

</xml>

in the visual studio it works just the way its supposed to be, but if i upload it on a report server and execute it there nothing happens. even no exception is thrown. the screen remains blank.

anyone got a solution or atleast a hint?

tia

found the problem:

the string i pass is only allowed to be about 1800 bytes large

problem with XML -> TABLE transfer.

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>

and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null

but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

You'll have to convert your data to DT_WSTR in the pipeline in order to insert to NVARCHAR.

-Jamie

|||

its already DT_WSTR

Under metadata - the pipeline

1. Name DT_WSTR

2. Address DT_WSTR

3. Age DT_UI1

problem with XML -> TABLE transfer.

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>

and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null

but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

You'll have to convert your data to DT_WSTR in the pipeline in order to insert to NVARCHAR.

-Jamie

|||

its already DT_WSTR

Under metadata - the pipeline

1. Name DT_WSTR

2. Address DT_WSTR

3. Age DT_UI1