July 24, 2013 at 7:47 am
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 🙂
July 25, 2013 at 12:28 pm
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
July 26, 2013 at 9:47 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 29, 2013 at 6:55 am
I usually resolve this issue by using an Execute SQL Task rather than a data flow
July 29, 2013 at 7:03 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2013 at 4:19 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply