April 9, 2008 at 6:29 am
My company is in the middle of converting our production systems from Access to SQL 2005. I don't have any problems migrating the tables and data using the SQL Server Migration Assistant, as long as it's a one-time shot.
However, it was decided recently that it won't be a one-time shot. As one phase of bringing the SQL environment up, we are going to be maintaining two environments for a couple of weeks: one (production) will be in Access and one (testing, QA, etc.) will be in SQL. I'm no fan of this arrangement, but the decision isn't mine to make - this is what I'm stuck with. Since it takes about an hour to migrate the data, using SSMA, and I will need to migrate the data at least once a day for the next couple of weeks, I'm looking for a more automatic way to do this.
"Ah-hah," you may be saying, "just use the Import Wizard in SQL!" Well, I tried that, but our Access databases don't use user-level security. Hence there is no workgroup file, and the Import Wizard scoffs at my pithy attempt. Nor is exporting the data from Access a viable solution; there are seven or eight MDBs that comprise our Access structure, and each of those MDBs may contain anywhere from 1 to 100 tables. SSMA works, as I said before, but our schema structure means having to migrate data from no more than ten tables at a time into one schema, changing the default schema for that MDB, migrating ten more, and so on, and then going into Access and manually upsizing tables whose names were changed as part of this process and then renaming them in SQL.
SSIS sounded like the logical solution, but I'm no programmer, and so my understanding of SSIS' capabilities is very limited. My idea was to build a project whose first step was to delete all the data from all the tables with an "Execute SQL Task" command and then set up the Data Flow to take each table using an OLE DB input connection and point it to its corresponding table in SQL using an OLE DB output connection. And this worked as long as I was using five or six tables. But then when I add more, I begin to get errors aplenty on the data flow. Most of them are something similar to this:
[(destination OLE DB connection) [3361]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'SSMA_RowID', table 'database.schema.table'; column does not allow nulls. INSERT fails.".
I had been getting other messages regarding constraint violations on the INSERT, but unselecting the 'Check Constraints' checkbox took care of those. I expect that unselecting 'Keep Identity' would solve this problem, but I don't know if the data would then be reliable. I'm probably going to distribute this package to one of the department heads who frequently refreshes our current QA environment, so just working around errors like I would if it were just me doing this is sadly not an option.
So I guess my first question is, what effect does not selecting 'Keep Identity' have on the integrity of the data? Second, is there a better way to accomplish my goal than the way I'm trying now? Thanks for any input.
April 9, 2008 at 7:02 am
SSIS is probably the best solution for this. I think you're on the right track.
"Keep Identity" will work so long as the Access tables have the identity data in them. So long as those are accurate, you won't have any problems with that.
If you have constraints being violated by the import, turning them off might not be the right solution. Assuming the data in the Access database is correct (which I hope is true, since it's your production database), then shouldn't the constraints be set to accept that data?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2008 at 7:24 am
Thanks again. Not all of the Access tables had identity columns - the SSMA_ROWID is an automatically-generated identity column that exists on the SQL server but not in Access. Adding an identity column in Access broke a number of our applications that were coded with SELECT * statements (I know, I know), so that workaround/fix is out.
The only constraints that exist currently are SSMA-generated "Zero length not allowed"-type constraints. They're possibly not even necessary in SQL, but that's not for me to decide. I'm not worried about violating those constraints, at least not for this portion of the project.
I'm getting a "Conversion failed because the data value overflowed the specified type." error now after unselecting 'Keep identity' on one field (and probably others, eventually) but all the field types in the advanced properties look like they match up. The exception is DT_DATE on the Access source and DT_DBTIMESTAMP on the SQL destination - which I wouldn't think would cause a problem going from a pure date up to a timestamp. Going from a datetime down to a date would cause problems, I know, but I don't expect that this should do it. I'll change that and see how it goes.
April 9, 2008 at 8:05 am
Have you tried simply using your Access front-ends with linked SQL Server tables? Assuming you're not overly fancy in your Access app - you could use them "as is" for a while with little disruption.
Meaning - upsize just the tables into SQL Server, get them indexed, and then create linked tables in the access MDB file.
Or is that what you're testing?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 8:17 am
What I'm trying to do is streamline the process of refreshing the data in the SQL server from the Access source for a few weeks until production data is in SQL as well. I'll be using linked tables then, but for pulling data into SQL I'm not sure how linked tables would help.
The production MDBs are located on a server at a colo facility, and if you were thinking of linking those tables back here to the SQL server, that's not really an option, as much as I'd like it to be. The link between here and there is too slow for the amount of traffic we have and would create an unacceptable lag time.
I am beginning to seriously consider a thousand monkeys on a thousand typewriters is my best solution, since I can't seem to fix the data type mismatch on these date fields.
April 9, 2008 at 8:39 am
There is a cast type function in SSIS. Are you already using that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2008 at 8:47 am
No. As I said, I'm not very familiar with SSIS. My data flow consists of an Access OLE DB connection connected to the SQL OLE DB connection, for each table. How would I use the cast conversion?
April 9, 2008 at 8:48 am
hmm..ugly. That's not going to work.
So - back to your original question. Keeping identities is important if you have them, so I'd keep that checked for those that have it. For those who don't, but who had SSMA add one for them - take a look at this blog post about how to add a script component to create the identity values for you.
http://weblogs.sqlteam.com/jamesn/archive/2008/02/13.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 9:44 am
A derived column might be the way to go on that. If you look up Cast in books online, in the Integration Services section, it will give you the data on how to convert data from one type to another.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2008 at 10:02 am
Sounds good, thanks! Even knowing WHAT to ask is a big step forward, here.
April 9, 2008 at 10:09 am
GSquared (4/9/2008)
A derived column might be the way to go on that. If you look up Cast in books online, in the Integration Services section, it will give you the data on how to convert data from one type to another.
There's also a specific "data conversion" task just for doing CAST type activities. Same look and feel as a derived column, just specialized for data type conversions.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply