Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Data source, Pls help me Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 2, 2014 1:34 AM
Points: 20, Visits: 150
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 :)





Post #1477066
Posted Thursday, July 25, 2013 12:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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
Post #1477664
Posted Friday, July 26, 2013 9:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1478075
Posted Monday, July 29, 2013 6:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 2,818, Visits: 2,552
I usually resolve this issue by using an Execute SQL Task rather than a data flow
Post #1478561
Posted Monday, July 29, 2013 7:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478565
Posted Thursday, August 8, 2013 4:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:26 PM
Points: 386, Visits: 622
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
Post #1482604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse