Easier way to convert Access Columns to SQL Columns

1
votes

I am converting an Access database to SQL Server tables. I use an OLE DB Source with a Destination of a ADO NET. The annoyance is converting the columns.

  1. For a string that needs to be shortened I use a derived column and use the left string function. EX (DT_STR, 15, 1252) LEFT (cm_type, 15)

  2. For a conversion that is just Unicode to non-Unicode I do a Data Conversion Transformation Editor.

Just wondering if I could be doing this faster somehow.

1
sql-serverms-accessssis
Is it slow or you think it may be faster without these transformations? If you copy a file from one machine to another - is it faster? Where SSIS runs: on SQL Server machine or on your development computer? - Stoleg
I just felt some of these conversions were time consuming. I was looking for a best practice. - joe

1 Answers

0
votes

You are using appropriate tools, so performance for these operations should be as designed by MS.

There is a "not so good/not advised" trick you can try - implicit conversion by SQL Server. If you know that there will be no truncation when column is shortened, then there will be no error. No error - remove that conversion. SSIS will complain with a warning, but will proceed.

Same thing for Unicode transformation. If you know that there are no Unicode symbols in Access - remove that conversion. SSIS will flag it with a warning as well, but will keep running until encounter a Unicode character.

To speed things up you can install SSIS on the same machine as SQL Server. Although some DBAs do not like managing complex installations and prohibit this. Copy Access db to the same machine as SSIS - it will save from slow network connection.