Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt
The txt file going from top to bottom is sort of grouped like this
Header Row (designated by €˜HD€™)
Corresponding Detail Rows for the Header Row
€¦..
Next Header Row
Corresponding Detail Rows
€¦and so on
http://www.webfound.net/rows.jpg
2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.
This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
The filenames of the txt files will vary and be based on one of the column values already in the header table.
Here is a print screen of my package so far:
http://www.webfound.net/tasks.jpg
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
http://www.webfound.net/DataFlow_Task_components.jpg
Let me know if you need more info. Examples of the actual data in the tables are here:
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg
View Complete Forum Thread with Replies
Related Forum Messages:
How To Separate Data's And Log's Files Location.
Hi everybody, On the time of installation SQL Server asking me where I wont to locate the DATA files and the PROGRAM files. It’s giving to me choice to put database AND log files on one disk and program files on separate. But what about to separate LOG and DATA files. I have RAID1 especially created on F: drive for LOG files and RAID 5 on E: for DATABASE files. When I have to separate that if not on the time of installation? How I can do that? Thanks, Miriam
View Replies !
Combine Two SQL Queries With Separate Where Statements
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
View Replies !
Header And Detail Records From DTS
I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be... HEADER DETAIL HEADER DETAIL HEADER DETAIL I am a little confused about how I can stagger header and detail using the same data. I appreciate any help you can give. Hopefully my explanation of the problem is understandable. Thanks, Val
View Replies !
Header And Detail Correlation
HELP! I don't have much more hair to loose. I have a text file that needs to be imported. The file may have 1 to 1000 inovice records with detail lines. However, the detail lines do not hold the invoice number, but the header line does. And of course, the detail can vary from one invoice to another. one can have 2 items, the second can have 5 items. Line 1 is the header line (line type, customer, event ect.) Line 2 is the detail line (line type, item, amount) I,12345,55555 D,abc,50.00 D,def,25.00 And so it goes. How can I get the detail for the appropriate invoice to match up with the header. I would like to use the customer number because the customer will only have 1 invoice per upload. I imported the file into a table in SQL which has an identity column. Help. I am lost with this one. Thanks
View Replies !
Detail Record Header
Hi, I'm new to SSRS. I was just wondering how do I make the header for a detail record appear once per grouping rather than once per detail record? Thanks.
View Replies !
How To Copy Detail Records To Another Header
Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11). So far I have two stored procedures: 1) sp_copyDetailsOne /*Create a recordset of the desired items to be copied*/ CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS SELECT itemId, itemPrice, itemDescription, itemQuantity FROM tblSalesItems WHERE (invoiceId = @invoiceIdFrom) OPEN @outCrsr 2) sp_copyDetailsTwo CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT /*Allocate a cursor variable*/ DECLARE @crsrVar CURSOR /*Execute sp_copyDetailsOne to instantiate @crsrVar*/ EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT /*Loop through the recordset and insert a new record using the new invoiceId*/ FETCH NEXT FROM @crsrVar WHILE @@FETCH_STATUS = 0 BEGIN /*Run the insert here*/ INSERT INTO tblSalesItems (invoiceId, itemId, itemPrice, itemDescription, itemQuantity) VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30) /*Fetch next record from cursor*/ FETCH NEXT FROM @crsrVar END CLOSE @crsrVar DEALLOCATE @crsrVar My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome. Thanks
View Replies !
SSIS Header And Detail To A File
Hi Anyone How to export a header and details data from two different table and export it in the below format ? RecordCount = 129 ------------> Header 001|Manager|2399.00|12 ------------> Detail Lines 002|Technican|1800.00|15 003|Mechanic|1500.00|18 ....... Total Amount = 180000.00 ------------> Footer Line I want to use the SSIS to do this job can anyone explain step by step. Thanks, Madhu
View Replies !
MS Access Detail Split Into Columns On Report
I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help. Thank you all! Timpy
View Replies !
Database Design Question, Header With Two Detail.. Pls Help
Hi All,I have a database design question, pls give me some help..I want to define tables for salesman's sales target commission . Thecommission could be given per EITHER sales amount of : Group ofProducts OR Group of Brand.e.g : the data example :For one salesman_A :product_1, product_2, product_3 etc.. => sales = $100 - $200 =>commission = 5%product_1, product_2, product_3 etc.. => sales = $201 - $400 =>commission = 10%Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>commission = 2.5%Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>commission = 5%Below is my table design, is this a good design or something is wronghere ? Thank you for your help.CREATE TABLE Sales_Commission_Header (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Note Varchar(30),Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,Sales_Commission_Group))Alter Table Sales_Commission_Header Add ConstraintFK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Sales_Commission_Detail (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Sales_From Decimal(12,2) ,Sales_To Decimal(12,2) ,Commission Decimal(5,2),Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,Sales_Commission_Group, Sales_From, Sales_To))Alter Table Sales_Commission_Detail Add ConstraintFK_Sales_Commission Foreign Key (Sales_ID, Sales_Commission_Group)References Sales_Commission_Header(Sales_ID,Sales_Commission_Group)CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4) Primary Key,Note Varchar(30))CREATE TABLE Commission_Group_Detail_Product (Sales_Commission_Group Char(4),Product_ID VarChar(10), -- This product_ID will be FKreference to master productConstraint Commission_Group_Detail_Product_PK PrimaryKey(Sales_Commission_Group, Product_ID))Alter Table Commission_Group_Detail_Product Add ConstraintFK_Commission_Group_Detail_Product Foreign Key(Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Commission_Group_Detail_Brand (Sales_Commission_Group Char(4),Brand_ID VarChar(10), -- This brand_ID will be FKreference to master brandConstraint Commission_Group_Detail_Brand_PK PrimaryKey(Sales_Commission_Group, Brand_ID))Alter Table Commission_Group_Detail_Brand Add ConstraintFK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)thank you,Tristant
View Replies !
How To Create A Constraint On A Header And Detail Tables?
Hello, I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable. Many thanks in advance.
View Replies !
[Resolved] Split Sum Into Separate Variables
I have a sp like this. The ttl_qty is the current sum variable. CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity) ELSE SUM(dbo.ProductionEvent.AlternateQuantity) END AS ttl_qty, dbo.SourceType.CompanySourceTypeId, I need to split the sum into separate variables, so when a 'PR' I need to add sum to a ttl_qty1 variable otherwise I need to add sum to a ttl_qty2 variable. How can I do this?
View Replies !
Combine Separate Date && Time Fields Into One Datetime Field?
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
View Replies !
Combine Separate Date && Time Fields Into One Datetime Field?
Good morning. I am importing an XLS file into one of my tables. The fields are: Date Id Time IO 12/22/2006 2 12:48:45 PM 9 12/22/2006 16 5:40:55 AM 1 12/22/2006 16 12:03:59 PM 2 When I do the import, I get the following: Date Id Time IO 12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2 12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1 12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2 Here are my doubts: 1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages? 2. If I don't combine them, should I use varchar or datetime data type? 2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are? Any comments or suggestions will be very much welcomed. Cheers mates.
View Replies !
(Revised)Database Design Question, Header With Two Detail.. Pls Help
Hi All,There is some additional info I forget on this same topic I just posted.I have a database design question, pls give me some help..I want to define tables for salesman's sales target commission . Thecommission could be given per EITHER sales amount of : Group of Products ORGroup of Brand. e.g : the data example :For one salesman_A :product_1, product_2, product_3 etc.. => sales = $100 - $200 =>commission = 5%product_1, product_2, product_3 etc.. => sales = $201 - $400 =>commission = 10%Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>commission = 2.5%Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>commission = 5%Below is my table design, is this a good design or something is wrong here ?Thank you for your help.CREATE TABLE Sales_Commission_Header (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Note Varchar(30),Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,Sales_Commission_Group))Alter Table Sales_Commission_Header Add ConstraintFK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Sales_Commission_Detail (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Sales_From Decimal(12,2) ,Sales_To Decimal(12,2) ,Commission Decimal(5,2),Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,Sales_Commission_Group, Sales_From, Sales_To))Alter Table Sales_Commission_Detail Add Constraint FK_Sales_CommissionForeign Key (Sales_ID, Sales_Commission_Group) ReferencesSales_Commission_Header(Sales_ID, Sales_Commission_Group)--------------------------------------------** ALTERNATIVE _1 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4) Primary Key,Note Varchar(30))CREATE TABLE Commission_Group_Detail_Product (Sales_Commission_Group Char(4),Product_ID VarChar(10), -- This product_ID will be FKreference to master productConstraint Commission_Group_Detail_Product_PK PrimaryKey(Sales_Commission_Group, Product_ID))Alter Table Commission_Group_Detail_Product Add ConstraintFK_Commission_Group_Detail_Product Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Commission_Group_Detail_Brand (Sales_Commission_Group Char(4),Brand_ID VarChar(10), -- This brand_ID will be FKreference to master brandConstraint Commission_Group_Detail_Brand_PK PrimaryKey(Sales_Commission_Group, Brand_ID))Alter Table Commission_Group_Detail_Brand Add ConstraintFK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)** ALTERNATIVE _2 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupNote Varchar(30),Constraint Commission_Group_Header_PK Primary Key(Sales_Commission_Group,Group_Type))CREATE TABLE Commission_Group_Detail (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupProduct_Brand_ID VarChar(10),Constraint Commission_Group_Detail_PK Primary Key(Sales_Commission_Group,Group_Type, Product_Brand_ID))Alter Table Commission_Group_Detail Add ConstraintFK_Commission_Group_Detail Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group, Group_Type)The PROBLEM here is : with Product_Brand_ID , I CAN NOT make foreign keyinto both Master Product and Master Brand.So which one is better design ?split the Commission_Group_Detail into Two tables, product and brand , andmake the FOREIGN KEYto master product and master brand (previous mail)ORcombile Commission_Group_Detail for Product and Brand into one table likeaboveand NOT make any FK to master Product or Brand ?Thank you for your help,Tristant
View Replies !
Should Be Simple - FLAT FILE - HEADER / DETAIL LINES
I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class. 10001,2005/01/01,some more data SOME PRODUCT 1, 10 SOME PRODUCT 2, 5 Can somebody place give me some guidance on how to do this in SSIS?
View Replies !
Transform Header And Detail Flat File Into One Table
I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read. The file looks something like this: FINNEY,RONNIE 0001005420 Mon 09/03 700a HOL 8.00 Tue 09/04 630a*E 326p 8.50 8.50 Wed 09/05 645a 330p 8.00 16.50 Thu 09/06 639a 2.40 18.90 HALL,MARK 0001005601 Mon 09/03 700a HOL 8.00 Tue 09/04 608a*E 257p 8.40 8.40 Wed 09/05 601a*E 259p 8.50 16.90 Thu 09/06 606a*E 3.30 20.20 JONES,WILLA JEAN 0001005702 Mon 09/03 700a HOL 8.00 Tue 09/04 556a*E 326p 9.10 9.10 Wed 09/05 600a*E 328p 9.00 18.10 Thu 09/06 554a*E 3.50 21.60 So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?
View Replies !
Tricky Schema Question - Dimension Can Split And Combine Over Time
Hi all,I'm working on the schema for a database that must represent data about stock& bond funds over time. My connundrum is that, for any of several dimensionfields, including the fund name itself, the dimension may be represented indifferent ways over time, and may split or combine from one period to thenext.When querying from the database for an arbitrary time period, I need the datato be rolled up to the smallest extent possible so that apples can be comparedto apples. For instance, if the North America region becomes 2 regions, USAand Canada, and I query a time period that spans the period in which thissplit occurred, I should roll up USA and Canada and for records in the periodthat has both, and I should call the result something like "(NorthAmerica)(USA/Canada)" in the output. The client specifies that the dimensionoutput must represent all dimensions that went into the input.Of course, I have to account for more complex possibilities as well, e.g.Fund-A splits into Fund-B and Fund-C, then Fund-C merges into Fund-D producing(Fund-A/Fund-D)(Fund-B/Fund-C/Fund-D)(Fund-B/Fund-D)I can think of several ways to handle this issue, and they're allextraordinarily complex and ugly. Any suggestions?Thanks,- Steve Jorgensen
View Replies !
Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it. In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory. Linda Here is the old VBScript Code: Public Sub Main() Option Explicit Function Main() Dim MovementDataDir Dim MovementArchiveDataDir Dim MovementDataFile Dim MovementArchiveDataFile Dim FileNameRoot Dim FileNameExtension, DecimalLocation Dim CurMonth, CurDay Dim FileApplicationDate Dim fso ' File System Object Dim folder Dim FileCollection Dim MovementFile '====================================================================== 'Create text strings of today's date to be appended to the archived file. FileApplicationDate = Now CurMonth = Month(FileApplicationDate) CurDay = Day(FileApplicationDate) If Len(CurMonth) = 1 Then CurMonth = "0" & CurMonth End If If Len(CurDay) = 1 Then CurDay = "0" & CurDay End If FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate) '===================================================================== ' Set the movement data directory from the global variable. MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value fso = CreateObject("Scripting.FileSystemObject") folder = fso.GetFolder(MovementDataDir) FileCollection = folder.Files ' Loop through all files in the data directory. For Each MovementFile In FileCollection ' Get the full path name of the current data file. MovementDataFile = MovementDataDir & "" & MovementFile.Name ' Get the full path name of the archive data file. MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name DecimalLocation = InStr(1, MovementArchiveDataFile, ".") FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1) FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1) MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension If (fso.FileExists(MovementDataFile)) Then fso.CopyFile(MovementDataFile, MovementArchiveDataFile) ' If the archive file was coppied, then delete the old copy. If (fso.FileExists(MovementArchiveDataFile)) Then fso.DeleteFile(MovementDataFile) End If End If Next fso = Nothing folder = Nothing FileCollection = Nothing Main = DTSTaskExecResult_Success End Function
View Replies !
Combine Raw Files Using Range
HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition: RawFile1.Date <= RawFile2.Date Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join? Thank you, Ccote
View Replies !
Restoring Separate Differential Backup Files
Hi, I'm making backups of the database by first making a full backup and then differential backups. The differentials are backed up to separate files. Restore of the full backup works fine, but I can't restore a differential backup. In Management Studio Express, I first do a full backup restore with option NO RECOVERY and then try to restore a differential backup. But this failes with the message: "This differential backup cannot be restored because the database has not been restored to the correct earlier state." Is it possible to restore a differential backup that is backed up to a separate file?
View Replies !
Importing .txt Files With Separate Definition File
Hi all, I receive data via FTP to our webserver nightly as .txt files and .dic (if anybody is familiar with idx realtor websites, that's what this data is). I've learned recently that I'm not going to be able to use Access to import or link to this data, so I'm trying to get my feet wet with SQL. I have been practicing importing text files into SQL db, but I notice that the dts imports everything as varchar 8000, and that you can edit that. I've got a .dic file that accompanies every .txt file that contains definitions of each fieldname, fieldtype & length & I was wondering how to import that data as well, without having to manually retype everything. I would be happy to email these text files to anybody willing to take a look. Thanks, Carrie
View Replies !
How To Combine Log Files && Delete Secondary
I have a database with 2 log files VE_log and VE_log2 (the split is left from legacy system with limited disk space). I want to eliminate the 2nd log. I've tried SHRINKFILE (EMPTYFILE), which of course reduces the file to the default minimum pages, SHRINKFILE (TRUNCATEONLY), SHRINKDATABASE (VE), and in every case the ALTER DATABASE REMOVE FILE command fails cause the log isn't empty. Ideas??? Thanks, Al
View Replies !
Combine Multiple RDL Files Into One RDL File
Hello, I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how? Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated. Thanks a lot. Let me know if the question is not clear. Mannu.
View Replies !
Export A Series Of Queries To Separate Excel Files Using DTS
I'm trying to find away to a export a series of queries to separate excel files. The only difference between the queries is an EmployeeID number. I've set up a DTS package that contains each query that I can run individually, but I'd like to run one DTS package that saves each excel file separately based on the EmployeeID. Here is my current Query: 'E705327' is the EmployeeID SELECT Sales.Quarter02.[ProjectName ], Sales.Quarter02.[ReportNo ], Sales.Quarter02.[Valuation ], Sales.Quarter02.[Stage ], Sales.Quarter02.[ProjectType ], Sales.Quarter02.[Address ], Sales.Quarter02.[City ], Sales.Quarter02.[County ], Sales.Quarter02.[State ], Sales.Quarter02.[ZipCode ], Sales.Quarter02.[GC ], Sales.Quarter02.[Architect ], Sales.Quarter02.[Consultant ], Sales.Quarter02.[ConsultingEngineer ], Sales.Quarter02.[ElectricalEngineer ], Sales.Quarter02.[MechanicalEngineer ], Sales.Quarter02.[Owner ] FROM Sales.SalesRep INNER JOIN Sales.CountyEID INNER JOIN Sales.Quarter02 ON Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[County ] OR Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[City ] ON Sales.SalesRep.EID = Sales.CountyEID.Rep1 WHERE (Sales.SalesRep.EID = 'E705327') ORDER BY Sales.Quarter02.[Valuation ] DESC
View Replies !
Automating Report Execution - Saving To Separate Files
I'm trying to do something which I hope can be accomplished relatively simply. I have a report similar to bank statements let's say. When run, it currently prints out each person's statement into one file, with page breaks sepearating each person's statement. What I need to do, is when the report is run, save each person's report into a seperate file for the purpose of emailing to them later. I could easily modify my report to just output for one particular person, but I'm not sure if there's a way to "bulk render" all the reports and have them saved to sepearate files. I should also add that I'm using an MS Access Data Project (ADP) as the front end to my app - connected to a SQL Server 2005 DB. I currently display the reports by embedding a web browser object into an Access form and rendering the report via HTML. Thanks in advance, H
View Replies !
File System Object - Combine 3 Files Into 1
I have an export file that updates each night and dumps into prices.txt I have to send them to an ftp site that processes them automatically if they're formatted correctly. They contain the same header information (saved in header.txt) and footer information (saved in footer.text). I then combine them all header.txt + prices.txt + footer.txt = glpcwholesale.prn The script below is what I have so far but it isn't working and errors out on line 23. Any help would be great. Function Main() ' OpenTextFile Method needs a Const value ' ForAppending = 8 ForReading = 1, ForWriting = 2 Const ForReading = 1 Const ForWriting = 2 Const ForAppending = 8 ' Create the File System Object Dim objFSO1, objFSO2,objFSO3,objFSO4 Set objFSO1 = CreateObject("Scripting.FileSystemObject") Set objFSO2 = CreateObject("Scripting.FileSystemObject") Set objFSO3 = CreateObject("Scripting.FileSystemObject") Set objFSO4 = CreateObject("Scripting.FileSystemObject") 'Creating string references to file locations Dim strDirectory, strFileHeader, strFilePrices, strFileTrailer Dim strFileGLPC strDirectory = "C:DataGarrowPRN" strFileHeader = "header.txt" strFileTrailer=" railer.txt" strFilePrices="prices.txt" strFileGLPC='glpcwholesale.prn" 'Creating File Objects Text File Reference Dim objFileHeader, objFilePrices, objFileTrailer, objFileGLPCWholesale Set objFileHeader = objFSO1.OpenTextFile _ (strDirectory & strFileHeader, ForReading, True) Set objFilePrices = objFSO2.OpenTextFile _ (strDirectory & strFileTrailer, ForReading, True) Set objFileTrailer = objFSO3.OpenTextFile _ (strDirectory & strFilePrices, ForReading, True) Set objFileGLPCWholesale = objFSO4.OpenTextFile _ (strDirectory &strFileGLPC, ForWriting, True) Do While (objFileHeader.AtEndOfStream <> True) objFileGLPCWholesale.WriteLine(objFileHeader.ReadL ine) loop objFileGLPCWholesale.Close Set objFileGLPCWholesale = objFSO4.OpenTextFile _ (strDirectory &strFileGLPC, ForAppend, True) Do While (objFilePrices.AtEndOfStream <> True) objFileGLPCWholesale.WriteLine(objFilePrices.ReadL ine) loop Do While (objFileTrailer.AtEndOfStream <> True) objFileGLPCWholesale.WriteLine(objFileTrailer.Read Line) loop objFileHeader.Close objFilePrices .Close objFileTrailer .Close objFileGLPCWholesale.Close SET objFileHeader = NOTHING SET objFilePrices = NOTHING SET objFileTrailer = NOTHING SET objFileGLPCWholesale = NOTHING Main = DTSTaskExecResult_Success End Function
View Replies !
Conditional Split - 0 KB Out Files
Hi, I'm exporting data to different text files depending on a condition. My "conditional split" looks like @[User::Variable0] == 0 @[User::Variable1] == 1 @[User::Variable2] == 2...........etc... I've about 8 output files from the "conditional split". For each run, only one condition is valid. When I run the package with value 0, I need to get only one output file with condition 0's data. That's working fine but I'm getting all other output files with 0KB, which I don't want. I'm getting like: Var0.txt ---------------> 2KB Var1.txt ---------------> 0KB Var2.txt ---------------> 0KB Var3.txt ---------------> 0KB ........... etc. How can we eliminate those 0 KB files? Thanks.. Siva.
View Replies !
Can I Split A Long .sql File Into Multiple Files?
I have one really long .sql file I'm working on. It's actually a data conversion type script. It's gotten really cumbersome to work on as long as it is. I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks
View Replies !
Selecting Detail Based On A Sum Of The Detail Lines
I am listing detail transaction lines in a table sorted by account and order number. the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order. I was trying Group by and Having but this doesn't seem to do what I need. Being relatively new to Reporting services, any nudge in the right direction would be useful.
View Replies !
FOR EACH Loop To Export Files Based On Header Record
Hey guys,, Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated.. I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together. I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one.. Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first.. Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part.. thanks again, scotty
View Replies !
Show Detail Data On A Matrix Rather Than Aggregate Data
Hi all experts, I have a result set like: Project Milestone Info Project1 M1 Info1 Project1 M1 Info2 Project1 M2 Info3 Project2 M1 Info4 Project2 M2 Info5 Project3 M1 Info6 I need to create a report like: M1 M2 Project1 Info1 Info2 Info3 Project2 Info4 Info5 Project3 Info6 But while I use matrix to build this report, I got the result like: M1 M2 Project1 Info1 Info3 Project2 Info4 Info5 Project3 Info6 The report will not show the multiple records on the row group "Project" like item "Info2". After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like: M1 M2 Project1 RowNumer Info1 RowNumer Info1 RowNumer Info3 Project2 RowNumer Info4 RowNumer Info5 Project3 RowNumer Info6 It would be much appreciated if anyone could give me some hints to complete the report I need. Thanks in advance, Steve Wang 2007/12/12
View Replies !
TSQL - Trim String Containing Both Data And Value Into 2 Separate Data Fields
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date. 378 9/05 388 9/05 4/05 1/06 606 1/06 646 76 5/05 100 1/05 118 8/05 129 8/05 9/05 342 05/3 123 1/07 4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006 and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone. I will appreciate any good ideas, Thanks in advance, Aldo.
View Replies !
Joining Summary Data With Detail Data
I know similar questions have been asked but I wanted to try my luck that my issue is somehow different. I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all. Any one have ideas?
View Replies !
Data Replication History Detail
We are using a SQL Server 2000 Replication. I'm using the Merge Agent History Screen to retrive Informacion about replication sessions, is there any other screen to know exaclty which datawas replicated on each session?Or at least to know the script generated on each session? Thanks
View Replies !
Separate Data
Hi guys, How can I separate the data that is alpha numeric in type? FloorNumber 8A 8B 8C 11A 11B 12 13 14 15A I need to separate the 8 from A in 2 columns just like .... UnitLevel UnitCode 8 A 8 B And if the floor numbers do not have "letters" then let it be. Thanks.
View Replies !
Newbie Question Regarding Data Reports And Detail Headers.
Good morning, I am working on a data report in visual studio (to reporting server). the report shows product sales by date and groups by date with a toggle to aggregate sales up by date. my question is this .... is it possible to get the report to show the header value for the toggle items INSIDE the group itself as a header rather than outside the group as a group header. i guess it would be called a detail header. i hope this makes sense. thanks Daniel.
View Replies !
Combined 2 Data And Separate Them
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
View Replies !
Separate Data In One Field
I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name. Example David Michael Carol Anne Is there a way in a query to look for the blank space and separate the names?
View Replies !
Take Data, Write Down, And Separate With A , (comma)
Hello there,How can i take data out of my database, put them into a textbox and then separate with a comma..An example:----------------------------------| column Email || mail1@email.com || mail2@email.com || mail3@email.com |----------------------------------Put them into a textbox and separate with a , (comma)-------------------------------------------------------------------------------------| mail1@email.com, mail2@email.com, mail3@email.com |-------------------------------------------------------------------------------------Anybody who know how I can do that? :S
View Replies !
Separate Data Stuff From Applications
Hi, This is probably for most of you a very basic question. My goal is to define in a seperate instance from application all infos related to DataSets and DataAdapters that are needed when connecting to a specific database. Why? First, I would like to be able to have in a single instance everything needed when hooking to a SQL Server Express database and in an other everything needed when hooking to a SQL Server CE database. Then, from application, I would like to be able following a few basic configuration steps to choose from one or the other, or even both in specific cases. Second, I want to be able to seperate Database related stuff from application so that I can re-use everything from any other application when needed. I've read several books, articles on the subject and would like to have your opinion as several approaches were illustrated through them. What would you use for this: - Component class? - Class library? - or else? Any good articles on the subject? Thanks in advance for sharing, Stéphane
View Replies !
|
TRACKER
