Did you ever get this message: “Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. ...” My Avanade Colleague Marcel Franke invested some time and found out that this might be a problem with line breaks in the SQL command… Especially when you use /* comments */ in the SQL… We’re not ready for an official bug report, yet, but I came across that problem more than once and now I have an idea where to look for…
Thomas
We investigated about a solution for that… Finally my Avanade colleague Tom Lichtenstein came up with an approach weeks ago which worked perfectly… Now I finally find some time to blog about that and: well, I’m not the first one…
Did you ever want to do a Cross-Join with the Merge Join transform? There isn’t a feature like that… What you have to do is add a “dummy” column (i.e. by a Derived Column transform) in both pipelines, sort by that column and join them. That’s expensive (because of the sort) and more a hack than anything else… Perhaps you can do the Cross-Join in your SQL query of the source instead… Perhaps you can at least add the dummy column in your source queries (that works if you have two different sources you want to join) and change the sort property in the output so that SSIS things the source is already sorted by that column (so you don’t need the Sort transform anymore).
One thing you should do for sure: vote for a change in the Merge Join transform so that we’ll see a Cross-Join in the future… https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=161154
Thomas