I work for a poultry science company and we generate about 15 csv files per
day from an egg scale we use to grade and package. One csv file is one
record, and each row represents a field much like a column does in a table.
I believe it's called a line-dependent csv.

The staff member who operates the scale enters header information to
describe each csv file generated on the scale: which flock, which farm, date
the eggs were layed, etc. This information is stored in a SQL Server 2000
table.

We are currently trying to integrate the weigh data into our third-party
production software for analysis by our production team.

The third-party vendor requests that the header data in SQL Server be
appended to the csv for their data transformation utility.

Should I unite the two files using a linked server, OpenRowSet or DTS?

I've been boning up on all three options, but would really appreciate some
direction from a seasoned professional before I embark on my journey.

Thank you!

Hi Caroline,

Welcome to use MSDN Managed Newsgroup Support.

From your description, my understanding of this issue is: You want to
append some data which retrieved from SQL Server to several CSV files. If I
misunderstood your concern, please feel free to point it out.



You can transfer the data to the CSV file by using the transform data task
in the DTS package. And you can schedule the DTS package to run every day.

Hope this will be helpful.

Sincerely yours,

Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Thank you, Wei Lu.

Can I bend your ear a bit more?

QUESTION:

<blockquote>Do the parameter options for the destination object in DTS allow
me to specify a line-dependent file in which each line in the csv file maps
to a field in SQL table?</blockquote>

This is the first CSV file I've worked with which does not store one record
per line, comma or tab delimited.

When I append the data from the SQL Source to the CSV Destination, I get this:

Farm Flock LayDate WeighDate DeliveryDate ExportItem
AverageWeight
TotalWeight
Min
Max

The SQL fields are written horizontally, while the CSV fields are written
vertically.
My problem is that the CSV file stores one field per line, not one record
per line.

Do I need a schema.ini file in the directory where the CSVs are stored to
explicitly tell the DTS which field is stored on which line?

Lastly,

Below is a summary of my design flow. You don't have to comment, but if you
see something that screams "Failure" would you point it out?

Here goes:

Each CSV file is one record. I will first read the first filename in the
directory into the parameter @filename, then select the matching record from
SQL (within the DTS) WHERE the field "FileName" = the CSV filename, then
append the CSV with the SQL data, save it, and iterate through the directory
repeating the same process.

Do I read the CSV filename by connecting from DTS package and selecting the
filename, or use a stored procedure to retrieve and iterate the file names in
the directory--this stored procedure being executed when the DTS launches?

I'll assume that DTS is superior in performance to linked server and
OpenRowSet when a daily transformation is required since you did not comment
on those two approaches.

Thank you very much.

Best regards,

Caroline

"Wei Lu" wrote:

Hi Caroline,

Thank you for state the issue clear.



I suggest you to use a store procedure to retrieve the directory
information and execute it in the DTS package.



DTS package is totally different to Linked Server and OpenRowSet.



DTS is a service used to import/export and transform data from disparate
sources into single or multiple destinations supported by DTS connectivity.

Linked server allows SQL Server to execute commands against OLE DB data
sources on different servers(including files).

OPENROWSET function is most like Linked server and you can use it to access
remote server or other OLE db data source.

Hope this information will be helpful.

Sincerely yours,

Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

This is very helpful, Wei.

Thank you.

"Wei Lu" wrote:

Hi Caroline,

Gald to hear the information is helpful!

If you have any questions or concerns, please feel free to let me know!

Have a good day!

Sincerely yours,

Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.