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
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18395 Visits: 20424
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4090 Visits: 2629
I usually resolve this issue by using an Execute SQL Task rather than a data flow
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27295 Visits: 13268
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1039 Visits: 902
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