DTS Create Report And email .

Hello,

I am trying to create a dts to schedule some reports and email it. The point is that i try to do that through Data Flow > Export to Excel....!!! Is there an other way to create reports? Cause i don't see any crystal reports or somenthing else!!!

Thanks
Stefanos
SSIS (I assume you're using SSIS, if not, you're in the wrong forum) does not create reports. You can save data to Excel, but it's just going to be rows & columns. No formatting, etc...|||

Stefanos -

I would recommend looking at SQL Reporting Services (SQLRS) for this need. SQLRS has two different report scheduling capabilities. SQL Server Standard Edition has standard subscription capability, while the Enterprise Edition adds to this a Data Driven subscription capability.

When a standard SQLRS subscription is created, this actually creates a job that is managed by the SQL Agent. If you needed SSIS to be able to execute that job/subscription, I believe it would be easy to do in SSIS.

Hope that might help you.

|||

Thanks for your reply,

I thought that through ssis i could create an excel report and edit the excel file through a script. My problem is that a want to create a report automatically and email it to a customer!! I think that Sql RS will help me doing that. As for the email forwarding, i think i will need ssis.

Thanks Stefanos

DTS - import flat textfile into two separate tables

I have a report that's created each day as a flat textfile.

Because I came from the Access world, I created a macro that imports
it with a schema that gives meaningful names to the various columns,
and then uses a query to massage some of the data for me (deletes the
first blank row and does a couple of calculations)

Then I use DTS to import the Access query as a table.

the textfile has a column called "File_num", and among several others,
a column called "Serial_num". (the file numbers represent shipments,
and sometimes there are more than one serial number in the shipment,
etc., so there is a separate line for every serial number)

Naturally, I would like to split this info into two tables..one that
does not contain the serial numbers and has a primary key on the
"File_num" column, and another table that would contain just the
"File_num" and "Serial_num" columns. That way I could relate them
later...but most importantly, it will give me a table where I can use
the "File_num" as my primary key.

What would be the best way to import these two tables from one source
textfile? The other thing that gives me problems is that the text
file has no column names, and the first row is always blank.

I'm very new to SQL and DTS and would appreciate any direction.

Thanks,

Larry

- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown.""Larry Rekow" <larry@.netgeexdotcom> wrote in message
news:3h1cj0976dtpkliussk5c1nr7jmubvt4rp@.4ax.com...
>I have a report that's created each day as a flat textfile.
> Because I came from the Access world, I created a macro that imports
> it with a schema that gives meaningful names to the various columns,
> and then uses a query to massage some of the data for me (deletes the
> first blank row and does a couple of calculations)
> Then I use DTS to import the Access query as a table.
> the textfile has a column called "File_num", and among several others,
> a column called "Serial_num". (the file numbers represent shipments,
> and sometimes there are more than one serial number in the shipment,
> etc., so there is a separate line for every serial number)
> Naturally, I would like to split this info into two tables..one that
> does not contain the serial numbers and has a primary key on the
> "File_num" column, and another table that would contain just the
> "File_num" and "Serial_num" columns. That way I could relate them
> later...but most importantly, it will give me a table where I can use
> the "File_num" as my primary key.
> What would be the best way to import these two tables from one source
> textfile? The other thing that gives me problems is that the text
> file has no column names, and the first row is always blank.
> I'm very new to SQL and DTS and would appreciate any direction.
> Thanks,
> Larry
> - - - - - - - - - - - - - - - - - -
> "Forget it, Jake. It's Chinatown."

A common technique is to import the source file directly into a staging
table, then transform the data using SQL. This is often easier than trying
to implement complex transformations in DTS itself. In your case, you could
do something like this:

insert into dbo.FileTable
(file_num, col1, col2, ...)
select file_num, col1, col2, ...
from dbo.StagingTable

insert into dbo.FileSerialTable
(file_num, serial_num)
select file_num, serial_num
from dbo.StagingTable

Simon