JBlad160907


Hi,

I've got this feeling that something is missing in Integration Services. You can add a DSV in any Integration Services project, but you can't use it inside a data flow. Can someone please tell me why this is

Let me explain why i want this: In Dynamics AX you've can make perspectives. These perspectives are a combined set of tables. From these perspectives you can automatically deploy these as Report Models. The Reporting Services generates a DSV for you, based not only on physical tables but also on Business logic. So it generates the linking between the tables (based upon application logic), derrived enumerated tables, referred tables, etc.. So these generated DSV's are great to get a quick startup.

So repeating my question. Why can't i select a DSV as source What is the function of a DSV inside Integration Services

Hoping to receive an answer for this question.

Kind regards,

Johan Blad




Re: Data flow based on a DSV

Phil Brammer


JBlad wrote:

So repeating my question. Why can't i select a DSV as source What is the function of a DSV inside Integration Services



Not much. Most users here avoid using DSV's inside SSIS.

However, add a new connection manager object based on your DSV. (Right-click in the connection managers box, and select "New Connection from Data Source...") Then pick the DSV you want to connect to. From there, add an OLE DB Source (or a DataReader Source) and select the connection you just added.








Re: Data flow based on a DSV

Jamie Thomson

JBlad wrote:

Hi,

I've got this feeling that something is missing in Integration Services. You can add a DSV in any Integration Services project, but you can't use it inside a data flow. Can someone please tell me why this is

Can you expand on this Are you getting an error What behaviour are you seeing

-Jamie







Re: Data flow based on a DSV

JBlad

Well this actually is the part that I have the most problems with. When i select New connection from data source..., I can select the data source, but not the data source view.

I tried to do it afterwards with edit and the drop-down, but still can't find any DSV connection in there. So Jamie my problem is that i just can't connect to a DSV.

Well the other strange part is the way that i had to get my DSV in the project. Inside a report model project you have the choice to built a model and you can also add an existing one. Inside an IS project you can't add an existing one..... so what i did (and this might be helpful my problem) was the following:

Create a solution

create a report model project

add the report model and dsv (using add existing report model)

create a integration project

copy the dsv from my report model project (the only way is drag and drop)

(right mouse, copy works in an IS project for a DSV, but not in a Report Model project )

Add the ds and connection string

Now i can edit the DSV inside my IS-project, but i can not make a connection based upon my DSV.

Hope you can help me

Johan





Re: Data flow based on a DSV

JBlad

Hi,

I just tried to add a new DSV by creating it inside my Intergation Project, but it gives me the same behaviour. I can not select a DSV as a source for my connection manager. The version I use is 2005.090.2047.00.

Thought that the problem might be that it was a generated version (from within AX4) or that the copy paste action was the problem, see my answer to Phil.

Hope you can help.

Johan





Re: Data flow based on a DSV

jwelch

The steps to use a DSV:

  1. Add a data source to your project.
  2. Add a DSV based on the data source.
  3. In your package, add a connection manager by choosing New Connection from Data Source. Choose the data source you created in step 1 (not the DSV).
  4. In a component or task that requires a connection (Execute SQL, for example), click the drop-down for specifying the connection manager. You should see the data source you created. If you click the plus sign to expand it, you should see the DSV listed underneath it. Select the DSV to use it in this component.

A little complicated, but hopefully that will work for you.






Re: Data flow based on a DSV

JBlad

Hi Jack, Jamie, Phil,

I've tested it from scratch. Just made a complete new project, only one ds, only one dsv, based on only one table.

version 9.00.3042.00

Tried it with an Execute SQL task, couldn't see my connections (made inside connection manager based upon my ds)

Tried a component (ole db data source) first try couldn't see it, second try couldn't see it, third try: I see my connections, with their linked dsv's.

Tried it again in the Execute SQL task. All of a sudden i see my connections. But still no success. not in the editor, not in the properties. I can't get to the level of my dsv.

made a new Execute SQL task, still can not get to the level of the dsv.

Tried it on another environment (9.00.3027.00), absolutely no way i could even see my ds's, nor my dsv's in an ole db source component, not in an excute sql task.

Tried it on an data reader component and choose ado.net as a provider, and i could see my ds's, but no possibility for selecting a dsv.

I'm getting a bit frustrated here. Even tried saving, building, rebuilding, deploying, shutting down BIDS, nothing seems to get it to work the way i want it or is suggested in the previous answers.

Anyone got a clue on what i should do Any way of adding some screendumps here

Johan





Re: Data flow based on a DSV

JBlad

Hi i'm back.

Getting even more frustrated with the use of DSV's in integration services. So i've got one ole db data source component working to accept a dsv. Still don't understand why, because the connection based upon my ds when selecting built connection from data source (sql native) gets generated as a ado.net connection. So why i can choose it (and only choose it there) is a riddle to me.

But enough about that, i have one place i can choose it, so i should be a bit happy.......... BUT, when i choose to base my source upon a table i can select only the table in my DSV. So expanded the DSV with another table, and, joy to the world, i can select both tables. But i don't want a table, i want the combination of tables as th linking lays in my dsv. so i tried a SQL statement. Alas the linking part is not automatically generated on the basis of my DSV. But when i now select add table....... it doesn't give me the two tables in my DSV, but it gives me the complete set of tables based on my DS.

But, he, i now can select a named query automatically....... but there is no named query derrived from my dsv, because i can not select a named query.

So i think the usage of a DSV in Integration services is NULL. Unless i am doing something completely wrong or SP2 has got some undocumented new features........

Sorry, that i seem to be a bit negative, although i am completely positive about the SQL 2005 BI-solution..... For me this should be one of the great advantages of the tight integration between AX 4 and SQL2005. Because all the logic is inside the dsv, so i want to use it and not completely rebuilt the query........

So thank you for listening, hoping someone can answer my questions.

Johan Blad





Re: Data flow based on a DSV

jwelch

You're encountering the same reasons why many people don't use them, as Phil referenced.




Re: Data flow based on a DSV

JBlad

Okay, so a last try. To convince Microsoft that the usage of DSV's is actually really handy in Integration Services, i posted this suggestion. So please feel free to reply "I agree" to this:

http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx mid=1d766952-c1c0-4e52-8901-b699393cd033&dg=microsoft.public.axapta





Re: Data flow based on a DSV

Jamie Thomson

JBlad wrote:

Okay, so a last try. To convince Microsoft that the usage of DSV's is actually really handy in Integration Services, i posted this suggestion. So please feel free to reply "I agree" to this:

http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx mid=1d766952-c1c0-4e52-8901-b699393cd033&dg=microsoft.public.axapta

If you want to make Microsoft aware then the place to put it is connect.microsoft.com

-Jamie






Re: Data flow based on a DSV

Phil Brammer

Just an FYI - they work for me in an Execute SQL Task.

1 - Create the data source
2 - Create the connection to the data source (right click in connection managers, add new connection based on data source.)
3 - Chose the data source in my Execute SQL Task under the OLE connections. But really, it just depends on the connection type defined in the data source. In some cases, it'll show up under ADO.






Re: Data flow based on a DSV

JBlad

Hi Phil,

Tried it several times, different ways, but it just doesn't do it. And when i could select one, just once (see previous frustrations), the dsv acted like a normal Data Source. So no pre-defined links or whatever was visible.