Advice on Flat File Import

I’m looking for advice for the following scenario:

Import Source:Flat File – 2 columns (student#, lunch_bal)

Destination:SQL Table:

Trnpayuniq (PK)

Atype (all values = ‘S’)

Auniq ( from flat file – student#)

Trnamt (from flat file – lunch_bal)

Trnpayc (all values = 1)

I’ll truncate the destination table before each import.

Is the best way to create a temp table; get it populated

then update the destination table?Or

would the use of Merger (Merge Join) be the better approach.

Thanks for any direction.

Could you just added a Derived Column transform to add the constant values to your data flow?|||I'll try that. Thanks.|||

If it works, please mark the question as answered, per Phil's sticky post

Advantage of Temp Table

When I import data I first import it from a text file into a table of
it's own, then using some logic insert some of the records into a
permanent table.

I am considering having the table that the data from the text file is
placed in being there all the time and just clearing it out after I do
the import, or creating it and after using it then drop it, or using a
temporary table.

What are the advantages of a temporary table as opposed to creating a
regular table and dropping it after use?Not much difference between a temp table and permenant table. Security,
automatic distruction, and one or the other may faster depending on the
disk/file layout. I would ask why not just push it directly into the
destination? Also if the text is coming from another SQL available source,
why not skip both the text file and the temporary table?

It also may be faster depending on need for availability, space, and
indexing to create a new table and do select into based on a union all of
the two tables. Then rename the table.

<shumaker@.cs.fsu.edu> wrote in message
news:1114111315.338927.231880@.g14g2000cwa.googlegr oups.com...
> When I import data I first import it from a text file into a table of
> it's own, then using some logic insert some of the records into a
> permanent table.
> I am considering having the table that the data from the text file is
> placed in being there all the time and just clearing it out after I do
> the import, or creating it and after using it then drop it, or using a
> temporary table.
> What are the advantages of a temporary table as opposed to creating a
> regular table and dropping it after use?|||Thanks.

"I would ask why not just push it directly into the
destination?"

Because I have to do some logic based on what is not in the imported
data. If a certain record exists in my table, but not in the data
being imported, then I need to modify some flags in the existing
record. So I import into an empty table, and do updates "if not in"
imported table then update my existing table.|||>> What are the advantages of a temporary table as opposed to creating
a regular table and dropping it after use? <<

A regular table will port, can have constraints, DRI actions, etc. But
do not drop it after use; just clean it out before and after you load
the data for scrubbing.

Advanced Editor for DataReader Source / SQLServer 2005

I want to import data from a remote MySql Database

So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:

Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)

the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm

Thank you very much for your support

Martin

Could you try to run this query through this provider using any other client?

Thanks.

|||I has the same problem!
if you found the solutions,please tell me.Thanks for you.|||As Bob was asking above, do you get the same error when running your query through a different client, such as a C# application or a query tool? This information would help narrow down whether this is an issue related to the query, to ADO.NET or to SSIS.|||I am having this exact problem as well. The query I've entered in the SqlCommand field in the DataReader Source Edit dialog works fine from the MySQL command-line on other platforms, other boxen. It would then have to be related to .NET Provider for ODBC or SSIS.
|||I should be more clear: I'm using MySQL ODBC 3.51.15, the latest version. It comes with a command line utility (myodbc3m) I can use to manually verify the ODBC connection/configuration that SSIS is using to connect; and it works fine. This seems to exonerate the ODBC driver, ODBC configuration, and database server itself, I would think.
|||Downgrading the MySQL ODBC driver from 3.51.15 to 3.51.12 solved this problem for me. Doesn't make any sense to me.
|||Thanks for the feedback, and for going through this prosess of elimination. I'm not sure what the difference is here either (I don't do much with MySQL) but at least you've gotten past the error.

Advanced Editor for DataReader Source / SQLServer 2005

I want to import data from a remote MySql Database

So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:

Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)

the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm

Thank you very much for your support

Martin

Could you try to run this query through this provider using any other client?

Thanks.

|||I has the same problem!
if you found the solutions,please tell me.Thanks for you.|||As Bob was asking above, do you get the same error when running your query through a different client, such as a C# application or a query tool? This information would help narrow down whether this is an issue related to the query, to ADO.NET or to SSIS.|||I am having this exact problem as well. The query I've entered in the SqlCommand field in the DataReader Source Edit dialog works fine from the MySQL command-line on other platforms, other boxen. It would then have to be related to .NET Provider for ODBC or SSIS.
|||I should be more clear: I'm using MySQL ODBC 3.51.15, the latest version. It comes with a command line utility (myodbc3m) I can use to manually verify the ODBC connection/configuration that SSIS is using to connect; and it works fine. This seems to exonerate the ODBC driver, ODBC configuration, and database server itself, I would think.
|||Downgrading the MySQL ODBC driver from 3.51.15 to 3.51.12 solved this problem for me. Doesn't make any sense to me.
|||Thanks for the feedback, and for going through this prosess of elimination. I'm not sure what the difference is here either (I don't do much with MySQL) but at least you've gotten past the error.