incomplete file format - file cannot be opened

[MarkGoodwin]

in DTS i am creating a package so that i can schedule a sql script to run every day with its output saving locally as a csv file

ive created a "microsoft OLE DB provider for SQL Server" and a testfile destination with a link between the 2 containing the sql code

im lost when its asking for "transformations" details like source column and destination column - i want everything thats in the result of the script to save as a csv file, i can do this fine when i run it manually in QAnalyser "save as".

What am i missing / doing wrong?? - sorry if its really easy just that this is the first time ive had to use DTS

Thanks in advance
Mark

 

[MarkGoodwin]

ps if i change the destination back to the database it creates and pumps the data successfully into "new table"

change it back to test.csv and it fails "incomplete file format information - file cannot be opened

 

[gradley]

You need to initially define the output file columns based on the source table:

Right click on the link to Open the "Transform Data Task Properties". On the "Destination" tab, click [define columns] and then select [execute]. This should define source and destination columns.

Your Transforms tab should now show links between the source and destination.

Hopefully this helps!

 

[MarkGoodwin]

when i do this enterprise manager closes with no message.

any ideas?

Mark

 

[gradley]

Are you selecting field names in the script that you've created that will export to a file? The output file format uses field names or aliases to determine the format.

 

[MarkGoodwin]

here's the script - you tell me? - many thanks

set datefirst 1 -- Needed for weekly option
select distinct
b.usercode as 'Usercode',
sum(case when settypeid in (4353,4865) then 1 else 0 end) as 'Box Adds',
sum(case when settypeid in (4354,4866,4875) then 1 else 0 end) as 'Box Moves & Inventories',
sum(case when settypeid=4867 then 1 else 0 end) as 'Box Return Scans',
sum(case when settypeid=4868 then 1 else 0 end) as 'Box Pick Scans',
sum(case when settypeid in (4870,4872) then 1 else 0 end) as 'Box Delivery/Access Scans',
sum(case when settypeid in (4871,4873) then 1 else 0 end) as 'Box Pickup/Receive Scans',
sum(case when settypeid=4874 then 1 else 0 end) as 'Box Destruction Scans',
sum(case when settypeid in (5121,5122) then 1 else 0 end) as 'Box Ordered for Delivery/Access',
sum(case when settypeid in (5125,5126) then 1 else 0 end) as 'Box Ordered for Pickup/Receive',
sum(case when settypeid=5123 then 1 else 0 end) as 'Box Ordered for Destruction',
sum(case when settypeid=5124 then 1 else 0 end) as 'Box Ordered for Permout'
from rsitemactivitycur a
inner join rsuser b on a.wanduserid=b.userid
inner join rsitem c on a.itemid=c.itemid
inner join rsaccount d on c.accountid=d.accountid
left join rsinvoicecycle e on d.invoicecycleid=e.invoicecycleid
where
/*remove the -- from the next line to look for activity today*/
--(a.activitydtime >= convert(varchar,getdate(),112) and a.activitydtime < dateadd(day, 1, convert(varchar,getdate(),112)))
/*Remove the -- from the next line to run for the current month */
--month(a.activitydtime) = month(getdate()) and year(a.activitydtime) = year(getdate())
/* Remove the -- from the next line to run for the current week assuming week is Mon-Sun ** Note that this assumes @@datefirst = 1 which must be set befor the query runs as the default is 7*/
datepart(wk,a.activitydtime) = datepart(wk,getdate()) and year(a.activitydtime) = year(getdate())
/*remove the -- from the next line to enter a date range. Format 'yyyy-mm-dd'*/
--a.activitydtime between 'yyyy-mm-dd' and 'yyyy-mm-dd'
and
/*exclude non-live accounts*/
(e.invoicecyclecode is null or
e.invoicecyclecode in ('m','a','q','6m'))
group by b.usercode
order by b.usercode

 

[gradley]

In the "Transform Data Task Properties" dialog. If you select the "Source" tab and click [Preview}, do you see the data with headings?

If so, do you see any columns without headings or with strange data?

 

[MarkGoodwin]

i see the data with the correct headings

 

[gradley]

Sorry to be taking this step by step but...

When you click in the destination tab, so you see those same column headings?

When you double-click on the connection for the Destination Text file and select 'properties', what options do you have set up?

 

[MarkGoodwin]

under destination tab - all it has is the "table name" which is the csv output file. Where its got name,type size etc its empty

connection props = existing connection = Text File (destination)
data source = test file (destination)
file name = blah blah blah.CSV

 

[gradley]

As I thought!

Is looks and sounds like the Query script you are using is possible too large for DTS to decifer.

Can you try a simple query against any table and see if the destination tab shows the fields you selected?

If this works, one option you can try is to put your script in a stored procedure and execute that.

 

[MarkGoodwin]

how do i go about a stored procedure?

really appreciate your time on this one

 

[gradley]

I actually came up with a better approach (I think)

Since you are able to successfully run this in Query Analyzer, write the results to a file with the same name that you are using in the DTS.

Once you have accomplished this, delete and recreate the Destination (text file) but instead if creating a new one, point to the one created from Query analyzer. When you go to the desination tab now, the columns should be there. I just tried locally and it worked fine.

Good luck!

 

[gradley]

Mark,

One other thing, Setup your Results output format in Query Analyzer to "Commma Delimited (CSV)" and select the print column headers checkbox.
This can be done from the "Tools - Options" menu.

1. General tab "Result File Extension" = .csv
2. Results tab "Results Output Format" = "Comma Delimited (CSV)".
3. Results tab "Print Column Headers" = checked.

When you run the query in analyzer, select "results to file" instead of to grid. This will create the file I spoke of above.

 

[MarkGoodwin]

ok ive got it sorted
deleted the link
re-added it and now when i click on destination tab - i click populate from source then execute

i didnt do this before and now ALL my columns are in the destination tab as you expected previously

Many Many thanks for your time with this - i owe you a pint!

thanks
Mark

 

[gradley]

Anytime!

Glad to help.