SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with Data source, Pls help me


Problem with Data source, Pls help me

Author
Message
Tony1234
Tony1234
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 160
Hi All,

Greetings!

I have requirement like,
-- We are maintaining queries in a table
-- By using single ssis Package we need to pull the data from the queries and place the rest data in destination.

I have implemented the package in the following way,

-- By using foreachloop, I am getting query to the variable, and using the same in Datasource by dataaccessmode as "SqlcommandFromvariable".

-- For the data flow task I have set the delay validation property to "True".


The problem I have faced is, we have Queries with different Column names.

Example:

For the first query I have the column name "Empid" with datatype as int. In my second query there the column name is like "optionId" datatype is uniqueIdentifier. I am getting error message as shown below

Error Message:
[ODS - GetDatfromProdServer [14]] Error: Column "optionId" cannot be found at the datasource.


Observations:

The external column name list is same as while creating the package, In my case it showing as "Empid". It is not changing based on the source query.

please help me on this.


Thanks,
Tony Smile
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 2582
A data source has to be able to access the metadata to expose its output columns. I haven't verified this, but I don't think the metadata of a data source can change between iterations of a loop. I suspect that when your loop runs the first time, the data source executes the first query and acquires the metadata of the result set. On the next iteration, the second query returns column names that differ from the established metadata. The data source can't resolve the columns of the second result set to the metadata of the expected result set and throws the error.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51206 Visits: 21155
wolfkillj (7/25/2013)
A data source has to be able to access the metadata to expose its output columns. I haven't verified this, but I don't think the metadata of a data source can change between iterations of a loop. I suspect that when your loop runs the first time, the data source executes the first query and acquires the metadata of the result set. On the next iteration, the second query returns column names that differ from the established metadata. The data source can't resolve the columns of the second result set to the metadata of the expected result set and throws the error.


Nearly correct. Meta data is set at design time, not run time.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7942 Visits: 2629
I usually resolve this issue by using an Execute SQL Task rather than a data flow
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62029 Visits: 13297
Instead of a data flow, you need an Execute SQL Task as Daniel mentioned.
Just put an INSERT INTO <table> before all your queries in your source table and execute it like a regular SQL statement.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2403 Visits: 907
or add FOR XML to the end of the query, that way the results will be in XML rather than their columns with the associated data types. the XML will just be treated as text so you can then move it to the next stage of processing safe in the knowledge that the data will be extracted and can be negotiated using x-query
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search