In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.
In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net Providers\Odbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DB\SQL Native Client) to the SQL database. Both databases reside on the same machine.
I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.
Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.
This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie
connect to the data conversion task before inserting into the destination or provide false for metadata in the properties of both the task.|||The issue is still with the Data Conversion. I've been reading many of the forums and it seems that, like myself, there are a lot of frustrated developers out there trying to make simple sql2000 dts packages work in sql2005 SSIS. Almost all of them point to the data conversion issue. What a real pain in the arse. I surely hope that Microsoft does something about this, SOON...
Something simple that would take less than an hour could turn into weeks and weeks of fruitless effort.
The client has a pervasive sql database that i did many tests on using sql2000 dts. There's approximately 150 tables. My testing went very well. I recommended to the client that they purchase a new server Win2003 R2 along with SQL2005, advising them that my development time in SQL2005, converting the tables with DTS would be a much better process with the upgrade (is it an really an upgraded when you have all this bickering on unicode (data conversion) issues).
I have spent countless hours trying to get this working in sql2005 ssis, with no success.
I wish in retrospect that i'd just had them get Win2000 & SQL2000. I would have been done with the project by now and i would have had a happy client. I feel like a damn fool, recommending a package that i thought would be a significant DEVELPOR improvement, and have yet to show any results. This does not bode well for others like me at all.
|||I have been testing and trying for days, almost a week trying to create a simple, single table SSIS package that i created in DTS in minutes. I'm very frustrated with how the whole process gets hung up in the data conversion.
I have a DataReader Source (an ODBC connection (System DSN) to a Pervasive SQL Database). It pulls the table in just fine. I have a DataFlow Task, using a Data Conversion. I have a SQL Server Destination (The table already exists in the SQL 2005 database).|||Please do this, I had did same thing in my package.
double click on data task flow,
then click on source task go to it's properties windows
there is a properties called ValidateExternalMetadata :- set is False
repeat for destination task properties.
|||
Your recommendation seems to work for Numerical Conversions.
BUT...
Not for string transformations. The strings come in as unicode DT-WSTR.
In the Data Conversion Transformation Editor, I have to manually create a transformation for each and every instance, from DT_WSTR to DT_STR. Once i've done this, it passes muster with running in Debug. This would still require lots of tedious manual, repetitive tasks.
Is there a way to force the DT_WSTR to DT_STR for all of these so i don't have to manually create a Transformation for each?
Or...
Do you have another suggestion on the STR issue that i might try?
Thanks too very much for your assistance!!!
