June 25, 2008 at 7:35 am
I have a SSIS import package that I run to import 6 access tables into a SQL Server database. Because of size constraints in Access, I moved these 6 tables to a new Access backend database, thinking (it seems foolishly) that I could simply change the source connection in the SSIS package. But when I tried, everything went to hell (errors re missing/mismatched column names), and so far best I can do is delete the original source and destination pairs and redefine them from scratch. Is there no better way?
June 25, 2008 at 8:15 am
That doesnt make sense.
I changed the connectionstring for an access db to a new mdb, and was able to re-use the metadata without a hitch.
The problem comes in when either your layout of your table has changed, or if you have changed a tablename.
SSIS is very metadata driven when it comes to changing things.
~PD
June 25, 2008 at 8:34 am
Thanks for testing.
My problem, is that the source table definitions have changed, and I am trying to get them to conform to the destination tables. (Longer story, the source tables, that are regenerated biweekly, were lost (??) from the source Access DB, and the backups that might have let me restore them could not be recovered.)
That should not have been a big deal, since:
1. They had been exported to the SQL Server, and
2. They are regenerated in total in my biweekly processing.
Except:
1. My first attempt to recover them (by exporting them back from SQL Server) left me with bad column definitions: the returned tables (originally Access text columns, to SQL Server nvarchar columns) came back as memo fields.
2. My second attempt, changing my original append queries (run after first deleting all rows of the Access destination tables) to make-table queries, lost most of the column specifications (e.g. a 9 position SSNO text field created a 255 position text field.)
So I am left having to check the column definitions, by hand, to get the Access source tables to match the destination SQL Server tables.
But I am having trouble getting SSIS to pick up my manual changes in the source.
June 26, 2008 at 5:09 am
Final resolution: Rather than trying to edit the column size definitions in the metadata, I had best results by just fixing the source table, and then deleting and recreating the input and output transforms. Through it all, I could find no list of mismatches other than the (much too small) tool tip box that was presented when mousing over the error icon in the output transform.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply