November 27, 2010 at 10:05 am
Hi,
I have a data flow task. The source in ole db. And the sql command calls a stored procedure
exec MyTable ?,?,?
I have mapped the paramters to the ssis package variable.
The SP is a dynamic query which returns different tables having different columns to each other.
Exec(@Sql) is what is returned.
Now, I dont have columns to map, so I set the 'ValidateExternalMetaData' property to False
However, for the flat file connection(to which this data has to be transferred to), which will have different
column numbers and name, cannot be created becasue of the absense of columns to map.
A new FlatFile needs to be generated for every table.
Can this be done? How?
Thanks,
Namrata
November 29, 2010 at 2:21 am
This was removed by the editor as SPAM
November 29, 2010 at 9:55 am
It can't be done directly within SSIS because you can only change the metadata surrounding the data flow tasks at design time. You cannot update them at runtime.
This means files with different data formats must have unique data flow tasks to go through. Banged my head on the wall for a few days recently before I found the microsoft sources to confirm this. Maybe in SQL 11, but not in 2k8 or 2k5 can this be done.
The workaround is to drop the information from the text file into a generic varchar(max) table and build your own file reader from the ground up. In a nutshell: not fun.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2010 at 10:06 am
You could try to execute the dynamic statement with FOR XML and handle it in an XML transformation. That way you're dealing with an XML document rather than a data set with unknown metadata.
December 1, 2010 at 10:40 pm
Yes, I found out from Microsoft too, that this cant be done using Data Flow task. There is a Data Flow task plus by Codec I think, which does the trick. However, I did not want to try that. Instead, I just BCP'd the files from within the SQL.
Waiting for SQl 11 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply