Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...
Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.
|||Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
MikeyNero wrote:
Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
|||
Hello,
I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)
I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
Cheer
Eric
|||MikeyNero wrote: Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"
In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.
Just my two-penneth worth.
-Jamie
|||
Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.
My advice? If you're using SSIS, don't use DSVs!
-Jamie
No comments:
Post a Comment