Create a flat file for each record in a table

I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium

Do you want any data in these files or just the file to be created? If you just want the file to be created you could aggregate the data and then use the FileExtractor to create the file (You would need to add 2 columns one for the file name and one for the file data (empty)). If you actually want the data to flow into these files then you would most likely want to use a script component.

HTH,

Matt

|||

It sounds like you want to run a exectue SQL task to get an ADO object which holds the records you want. Then for each of these you what to push thenm to a for loop and have a dataflow inside the for loop. In the Dataflow the source would be the ADO object and you can push it to the text file.

These pages should help a bit

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/2458.aspx

http://sqljunkies.com/WebLog/knight_reign/archive/category/458.aspx

|||Thanks!

CR from only code(VS 2005)

Hey gurus

I need to create a crystal report entirely from code

i have a form where a customer will add table(s) to a list box, depending on the added tables, a list of fields applicable to the respective table can be added to another list. On selection of each of these fields, values may be entered so as to filter the data displayed on the report.

From each of the availble fields that can be "Parametized", a group can be created, with ascending/descending order

Finally summaries can be added such as the sum of all amounts(total,VAT etc) to the end of the report
Report options like drill-down will need to be able to be set dynamically also.

so far everything above here is basicly how to generate my SQL statement, which won'y be that difficult.

But i have no idea how to make my report document show information the way i have described above.

left list < > right list (Panel with combobox, textbox , checkbox(enable/disable))

tables,field, groups are added/removed as depicted above
fields and groups have a panel next to each rightlistbox where parameters can be set like (< = > between like <> asc desc)

at the bottom of the form there are 3 buttons
Preview report data --> shows datagrid of Data retrieved via generated SQL statement
Preview Report --> shows a itempanel with items for each field/group/summary field
Create report - Creates crystal report and shows in report viewer on a tab created before the report begins creation

At this point, i am not very close to being far from completion
any help will be greatly appreciated
Thanks
Jsee attached file of GUI so you can get a better idea of what i am trying to do, sorry bout bad quality 100kb is not alot but sufficient|||I have made some progress on this

The code below shows the population of a dataset and subsequently binding it to a report, then showing that report in a report viewer control on a new tab created here also.

The report is completely blank. There is however data in the TRDS dataset



Private Sub MakeReport()

Dim TRDS As New DataSet

Dim ConString As String = ""

Dim CON As OleDb.OleDbConnection

Dim DA As OleDb.OleDbDataAdapter

Dim NewTabPanel As New DevComponents.DotNetBar.TabControlPanel

Dim RptUC As New ReportOnTab ' Usercontrol with crystal reportviewer on a panel

'Get Data

Try

TRDS = New Report1DataSet

If My.Settings.User_Claims_ManagementConnection_String = "" Then

ConString = My.Settings.Claims_Management_SystemConnectionString.ToString

Else

ConString = My.Settings.User_Claims_ManagementConnection_String.ToString

End If

CON = New OleDb.OleDbConnection(ConString)

DA = New OleDb.OleDbDataAdapter(FullSQL, CON)

'Fill the dataset

DA.Fill(TRDS, "Client")

'Make report on new tab

NewTabPanel.Name = "NewTab_Panel"

NewTabPanel.Text = "This is the new reports panel"

'Report creation

Report = New Report1

'Settings for the report

'Set datasource to the generated dataset

Report.SetDataSource(TRDS)

'At this point there is a blank report, with a dataset attached to it containing, one table and rows filtered by where in FULLSQL

'Set groups

'Add fields that will be shown to detail section

'Add summaries

'Apply formatting

'End of report creation

'Create tab on tab control

ReportTab.CreateTab("Name of this report", -1)

'Attach usercontrol to tab panel and make dock fill

ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1).AttachedControl.Controls.Add(RptUC)

ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1).AttachedControl.Controls.Item(0).Dock = DockStyle.Fill

'Reset the report creation form

ReportDefinitionsClear()

'Select the newly created tab

ReportTab.SelectedTab = ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1)

'Set source of the usercontrol report viewer to this report

RptUC.ReportViewer.ReportSource = Report

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub



now the FULLSQL variable is rather dynamic and is generated through use of the above picture's gui, thats 2000 lines, shud i post that to?

anyway, it works, all i need to do now is...

1.! create fields from the SELECT statement on the report

2.! create groups via the ORDERBY statement on the report

3.! Finally add any other summaries,special fields, and so on to the report

My question is how do i achieve such a feat,

How do i add groups via code to the detail section?
How do i add fields via code to my blank reports detail section?
Is it even possible?

So far have discovered that most things within the report1 class are readonly.

I have looked everywhere online, searched for 2 days and found nothing like this

Please help
Thanks
J

Couple questions about partitioning and performance gain

Hello,

I try to maximize cube performance with partitioning. As a test, I have created a very thight partition for one customer only and compare the cube performance between this customer and other customers in larger partitions.
I am a little confused because I do not see a difference.
I also note that when I include calculated measures in the result set, it really knock-down the performance. example, this calculated measure:

iif([Margin Type].CurrentMember.name = "Historical",
iif(isempty([Measures].[AMT]) or [Measures].[AMT] =0, null,
([Measures].[AMT] - [Measures].[Margin1 Cost])/[Measures].[AMT])
, iif(isempty([Measures].[AMT]) or [Measures].[AMT] =0, null,
([Measures].[AMT] - [Measures].[Margin1 Cost Inception])/[Measures].[AMT]))

Really slow-down the cube even for the customer that has a dedicated partition.
The expected result is a matrix of 10,000 rows for 4-5 attributes and 4 columns of measures plus 4 calcuted measures times 8 quarters. unfortunately, I need that much data.

I tried to set the non-empty behavior of this calculated measure to be both types of cost and removed the isempty or 0 test but did not see any change.

I also did both a 30% aggregation plus the user queries optimization.

The fact table is 6M rows, 2 of the dim tables are 300K rows. I run SQL2005 enterprise RTM (no sp1 yet, I did not test it). I use Excel 2003 pivot as front-end.

So is there any benefit in creating lot of small partitions to boost most likely users queries?

I am learning, reading and trying but I lack the practical experience.

Any insights?

Thanks

Philippe

Does this perform any faster?

Create Member Measures.yourcalcmeasure = 1 - [Measures].[Margin1 Cost Inception] / [Measures].[AMT];

(Measures.yourcalcmeasure, [Margin Type].Historical) = 1 - [Measures].[Margin1 Cost]/[Measures].[AMT];

(and try to deal with "divide by zero" errors in the client application)

Thank you

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.
Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Counting query

Hi,
I have two tables: table 1 contains customer information, and table 2
contains order information.
Table 2 is updated everytime a customer orders some goods. Therefore, a
customer, for example, can appear within table 2 on, say, a total of 5
occasions.
I would like a column in table 1 that tells me how many orders the
corresponding customer has placed in total. Is there anyway of linking
table 1 with table 2 to count the total number of orders a particular
customer has made? (i.e. in the above case 5)
Thanks for your time
Paul Evans
While you can do this it is usually not a good idea. The main reason is
that now you have extra work somewhere (most likely a trigger) to keep that
value up to date and in some cases it can get out of sync. It is usually
better to simply issue a SUM or COUNT against the orders table with a WHERE
clause that filters by customer id. You would normally have an index on the
customer id and the operation would be pretty simple. If you use this value
a lot and there are not a large amount of new rows added to the Orders table
you might consider using an indexed view that sums up by customer. See more
in BOL on Indexed Views.
Andrew J. Kelly SQL MVP
"Paul Evans" <paul_evans1@.btinternet.com> wrote in message
news:uuR3pWS5EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two tables: table 1 contains customer information, and table 2
> contains order information.
> Table 2 is updated everytime a customer orders some goods. Therefore, a
> customer, for example, can appear within table 2 on, say, a total of 5
> occasions.
> I would like a column in table 1 that tells me how many orders the
> corresponding customer has placed in total. Is there anyway of linking
> table 1 with table 2 to count the total number of orders a particular
> customer has made? (i.e. in the above case 5)
> Thanks for your time
> Paul Evans
>

counting distincts

This won't work...
SELECT count(distinct z.id, c.alias, c.type)
FROM customer c JOIN zsource z ON c.id=z.id
How can I count the number of distinct combinations of 3 separate fields?This should work :
SELECT count(*) FROM (
SELECT distinct z.id, c.alias, c.type
FROM customer c JOIN zsource z ON c.id=z.id) a
-Nags
"Dave" <dave@.nodomain.tv> wrote in message
news:#H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
> This won't work...
> SELECT count(distinct z.id, c.alias, c.type)
> FROM customer c JOIN zsource z ON c.id=z.id
> How can I count the number of distinct combinations of 3 separate fields?
>|||This is one way. There could be other better ways.
select count(RecCount)
from (select count(*) as RecCount
from customer c JOIN zsource z ON c.id=z.id
group by z.id, c.alias, c.type) d
"Dave" <dave@.nodomain.tv> wrote in message
news:%23H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
> This won't work...
> SELECT count(distinct z.id, c.alias, c.type)
> FROM customer c JOIN zsource z ON c.id=z.id
> How can I count the number of distinct combinations of 3 separate fields?
>|||Thanks much
"Nags" <nags@.DontSpamMe.com> wrote in message
news:O4lRaPcFEHA.2560@.TK2MSFTNGP12.phx.gbl...
> This should work :
> SELECT count(*) FROM (
> SELECT distinct z.id, c.alias, c.type
> FROM customer c JOIN zsource z ON c.id=z.id) a
> -Nags
> "Dave" <dave@.nodomain.tv> wrote in message
> news:#H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
fields?
>

Counting Customer Transactions

I have a question I'm hoping someone will be able to help me with - I've been working on it for a while and can't seem to find an obvious solution.

Basically I have a fairly standard transaction fact table, and customers may have made multiple transactions over the selected range.

I need to know how many customers made 1 order, 2 orders, 3 orders, etc. As far as I can tell, I'm effectively doing a count of a count.

It seems to me to be a fairly straightforward request, however I can't work out a way to do it in Analysis Services. If someone can help me out or point me in the right direction I would be very grateful

Thanks,
Matt

is the selected range fixed? if yes, you could create a set for this purpose.

If not, which i believe is the case, you can rely on the tool you're using as interface to do the filtering for you...

if you're writing an mdx query, did you try doing it in the where condition ?

|||

Hi Christina

Thanks for your response. You're right, the range isn't fixed, although I guess it probably could be for most purposes. Could you please give me an example of how you would do this with a set? I haven't had to use them yet.

The frontend we are using is web-based Dundas Charts for OLAP, and there is no provision for writing mdx queries. Ideally it would appear in the interface as just another attribute in my Customer dimension. Any other thoughts?

Thanks again for your help,

Matt

|||

i don't know if this is a good solution:

since you mentioned that it would appear as another attribute, then maybe you should create a dimension with the customer and the values per range. but the ranges should be predefined. you would lose the flexibility

this dimension would be based on a view in the DW based on this fact table. it should contain the counts and have its structure as such:

customer CountRange1 CountRange2 CountRange3 CountRange4...etc..

where the ranges are not overlapping, however they are the most used ranges. better if you have only 1 range, because on dundas, u'll have to add all these fields to use them as filter

i will think of another alternative and let you know..

Counter by Group

How can I create a counter (line#) by group were it resets = 1 whenever a new group starts.

Customer Line#

A 1

A 2

A 3

B 1

C 1

C 2

Hello,

Try putting this in your detail row:

=RowNumber("GROUPNAME")

Get the GROUPNAME by right-clicking on your group header row and select "Edit Group...". Copy the Name and place it in the RowNumber function.

Hope this helps.

Jarret

|||

This works as specified but it does not solve my real problem.

I have a query - customer master joined to ship-to's.

I am trying to use the RowNumber expression with a Parameter "Show Ship-To's" - Y/N

If Show = N and RowNumber <> 1 then I do not want lines 2 - X to show.

This works. However, it treats the display as if all the lines are there. Page 1 might show only 3 accounts, Page2 might show

10 accounts, Page 3 shows 5 accounts, etc, etc

Using a Parameter how can I tell it to include all the lines or just the 1st line of each group of customer#'s. If Ship-To = N

then each page should have 50 Master accounts with no ship-to data.

Account Ship-to

A

B

C

D

E
F

...

or

A 1

2

3

B 1

2

C 1

It's almost as if I need to condition the query to get the data from Customer Master or Customer Master joined to Ship-To

based on the Parameter "Ship-To" (value = Y/N). That way, the correct data is there before it ever trys to format the report.

Detailed explinations/examples needed as I have no knowledge of VB and Reporting Services other than what I can get out of a book.