My first post so if I make a mess of it go easy on me. I'm a "resting" contractor at the moment so I'm taking the chance to teach myself some BI.
I'm trying to create an SSIS package that simply copies data (1 column) from one database on my home server (SQL 2005 Developer edition, up to date with service) to another. I am using an OLE DB Source and a SQL Server Destination with no transformations in between. My problem is that the package seems to run OK, with no error messages and everything going green but no rows are getting copied. I have tried a Copy Column transformation in the middle but the same thing happens.
I thought the problem was that the source field was varchar(50) and the target field varchar(100) so I included a CAST in my SQL on the OLE DB Source to give me:-
select distinct cast(method as varchar(100)) as nmethod from mapmate
order by nmethod
but it didn't cure the problem.
All the mappings look OK and the metadata matches up so I'm a bit stumped. Can someone suggest a way to investigate this further - I don't really know where to go from here. If this was a work problem I'd use another source (eg a text file or Excel, which I've already done successfully) but it's the problems that you really learn from, right?
I've found the problem.
A data viewer showed no data coming from the OLE DB Source. My SQL in the source had a "Use dbname" statement in it. SSIS didn't object to this when doing a preview or on a "Parse Query" in the Source Editor and flagged no errors during execution. However, when doing a "Build Query" in the Source Editor I got an error message for the "use" statement. Taking the statement out allowed the data through.