Error 0xc0204016: DTS.Pipeline - SQL Server

It was just another week back. I've been asked to transfer data from SQL server to MS Access and Excel. It is been little longer time that I have used SQL Server. So I thought it as a challenge! I know there were tools to do the needful. However, the best tool was Data Transformation Services (DTS) which came with SQL Server 2000 in the old days. Now it is more improved and it bundles with SQL Server 2005 and the tool named SQL Server Integrated Services (SSIS).

Now for SQL Server 2005 Express edition you have to download this and install it separately.

Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB)

And later you can run it

Run "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

I thought work is going to be easy until I found the error messages I have received. I searched through the Net to find the solution. There were, however they were not simple as I thought.

Some were explaining to change the destination source file, such as to change the table column to memo. It is little confusing, since the tables are yet to be created.

I tried few SQL scripts too and nothing went well.

The reason this error message prompted was that one of the column field size is more than accepted at the destination file (in my case it is MS Access).

After reading this article (http://blogs.msdn.com/b/sqlazure/archive/2010/06/01/10018602.aspx) only I have realised that I did not try to change the source file column data type as required. By changing the particular table column data type longtext(8000) to varchar(max) allowed me to transfer the SQL server data to MS Access.

It was not easy. However I was able to end it.