SSIS: Multi-Record File Extract With 9 Record Types
Feb 26, 2008
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
View 3 Replies
ADVERTISEMENT
May 10, 2006
Hi,
I've just started looking at SSIS and have encountered what should hopefully be a simple problem to solve. I have a pipe-separated source file that looks like this (I've added Line numbers for simplicity):
Ln 01: HDR|FEED_CODE|31-MAR-2006
Ln 02: Tom|100|Jones|ZZ1 1ZZ|USA
Ln 03: Tom|200|Singer|
Ln 04: Tom|305||Red|Porche ||Lanzarote |Apple|Carrot| | |
Ln 05: Dick|100|Van Dyke|ZZ1 1ZZ|USA
Ln 06: Dick|200|Actor|
Ln 07: Dick|305||Blue|Ford||California |Tomato | |||Beef
Ln 08: Harry|100|Houdini|ZZ1 1ZZ|GBR
Ln 09: Harry|200|Escapologist|
Ln 10: Harryk|305| |Green ||Triumph |Poland|Banana|Sprout| | |
Ln 11: TRL|9
In addition to a header and footer records, this file contains three record types for each person.
Record types are identified by the second column.
Each record type has a different number of columns:
Type 100 has 5 columns
Type 200 has 4 columns
Type 305 has 12 columns
The Row delimiter for all records is the {CR}{LF} character
I've set up a flat file input source and specified {CR}{LF} as the row delimiter for both header and data rows and the "|" character as the field delimiter.
It appears that SSIS is assuming that because the first data row has 5 columns, then everything must fit that format too. So the {CR}{LF} character that separates lines 02 and 03 is interpreted as text rather than a separation character and all remaining | field separators after 305 are interpreted as text containing in the fifth column. SSIS is also complaining that the last row is incomplete.
A bit like this (I've used tildes to indicate column separation):
Tom~100~Jones~ZZ1 1ZZ~USA
Tom~200~Singer~{CR}{LF}Tom~305||Red|Porche ||Lanzarote |Apple|Carrot| | |
I've seen one other reference to this behaviour but the response seemed to be SSIS doesn't know which columns are missing. In this scenario, we don't have missing columns, rather, we have different types of record in a single file. in DTS I would effectively parse the file once for each record type thus:
if cStr(DTSSource("Col002")) = "100" then
DTSDestination("in_Name") = trim(DTSSource("Col001"))
...
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipInsert
end if
...not the most efficient solution I know but the load only runs once a month so this was an acceptable workaround.
DTS was never this fussy but I'm sure this is user error rather than an SSIS limitiion. Can someone please put me straight?
Many thanks,
Greg
View 7 Replies
View Related
Mar 27, 2008
I have used the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 9 datasources, ordering each on a field commmon to all.
I have created the required derived columns headers and have merged all the record types into a file.
The resulting file looks fine, except for the odd blank line between record types. Any ideas regarding cause and what to do to fix?
Any help is most appreciated!
View 10 Replies
View Related
Feb 21, 2008
I am using the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 2 datasources, ordering each on a field commmon to both.
I have created the two derived columns headers and am now moving on to the merge.
It is failing with the following error:
"the input is not sorted"
And whilst I definitely have an order by on the query, when I look at the metadata between the datasource and the derived column, the Sort Key Position items displays "0" for all my fields, I was expecting the sort field to have a "1" in this column. What am I missing?
Any help would be most appreciated!
View 7 Replies
View Related
Nov 16, 2006
ex. from source the file you want to split the record into two, the one with a clean record and the other one with duplicate records
View 3 Replies
View Related
May 14, 2007
I have a text file to import where there are three file types: a header which has info about who sent the file and begins with 'H', detail records that begins with D and a trailer record that begins with T and just has the record count following that. The fields are delimited by '*'. H, D and T records each contain a different number of fields.
I suspect that what I should do is to split this file into three separate files. I tried to do this with SSIS but ran into problems. If I make the output a file destination, it won't let me use that output as input for the next process. There are no arrows I can grab onto to link to the next transform.
This is my first SSIS package although I made hundreds of DTS packages a few years ago. I can't figure this out in DTS either.
This sounds like it should be an EASY thing to do.
View 3 Replies
View Related
Sep 27, 2006
A flat file I must parse has multiple record types in the same file that must be processed together. For instance, in the following example:
01TestStuff888
02TestStuff2
03TestStuff3
01TestStuff4
02TestStuff5
Each 01 record type has the records after it associated to it until the next 01 appears, so TestStuff would have TestStuff 2,3 related to it while TestStuff 4,5 belong together. In the example the 888 in the 01 record is the key to the group, but it does not appear in the following lines.
The problem is that each record type has different line formats, columns, etc, so they must be parsed differently. I have created a conditional branch on the first two characters, and written each record type out to a seperate flat file for that type, so that they can be imported again and parsed with the Flat File Source, but I am unsure how to relate them again. I tried appending the 888 to the other lines before they were written out, but I can't find a way to share the variable across the conditional split branches using a script component.
Does anyone have an idea how I could parse these files and keep the relationship intact?
Is there a way to tell the flat file wizard to use a different map based on certain characters?
Is there a way to share a variable across the different braches of a conditional split.
Strange question I know, but thanks for any help.
Travis
View 4 Replies
View Related
May 1, 2008
I'm using SSIS to import seven flat files (each containing a different record type) into a staging database. This part was easy.
Now I need to export the records from all seven tables into a single flat file structured in a nested hierarchy using common keys. (This format is required by the vendor for loading data into a new system).
I could use some ideas on the data transformations needed to combine all seven record types into an hierarchical record set which can then be written to my Flat File Destination. I'm currently looking at an article on SLQIS.com ("Handling Different Row Types In The Same File") which seems close to what I need, but they are importing (ref: www.sqlis.com/54.aspx ). I'm not sure if I should just reverse this for export or use something different. Any comments are appreciated.
Diagram of Record Hierarchy
typeA (parent key, ...)
typeB1 (parent key, childSet key, date, ...)
typeB2 (parent key, childSet key, ...)
typeC (parent key, childSet key, ...)
typeD (parent key, childSet key, ...)
typeE1 (parent key, childSet key, date, ...)
typeE2 (parent key, childSet key, ...)
The record types B1 through E2 form a complete set. Each set has it's own unique child-set key. There may be one or more sets for each typeA record (although it's possible that typeE records don't exist in the most recent set).
View 3 Replies
View Related
Jul 20, 2007
Howdy all,
I've seen several posts about reading and writing files that have different record types with varying column metadata. My particular file has 11 record types plus several header types and looks something like:
<Header1>
<Header2>
<Detail01-#1>
<Subdetail02>
<Subdetail03>
...
<Detail01-#2>
<Subdetail02>
<Subdetail03>
...
...
Since i need to get different detail and subdetail records, i can't really use the technique of 3 dest file connection managers found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87269&SiteID=1
I've tried using an exec sql to get the main detail records and then a forech ADO en umerator that would get the subdetails, but it all seems so kludgy. I'm starting to think that I should just write the bulk of the file creation code in a c# app instead of trying to smush this into SSIS. Opinions? Am I missing some trick in SSIS?
TIA,
-Peter
View 7 Replies
View Related
Apr 9, 2004
I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?
Sample multi-row records:
WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC
WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC
View 3 Replies
View Related
Jun 19, 2007
I am currently working on project where I need to insert, delete and update data from a text file I receive into multiple tables. So the file has multiple recordset types(50 to be exact) and each record has a a code to perform an -Update, Insert, or Delete to the destination table. Also when I receive the records they are not sorted. I need to sort the sets for each destination table and then read the the sorted set sequentially and perform the correct action.
Currently I am importing the record via flatfilesource into one column. I am using a script component here that would consist I guess 50 outputs including the fields needed for each table. The outputs are sorted by sortkey field and when I add the record to the output I perform data type transforms needed for each field(most are strings but I need to convert some dates, numbers ect..).
***is there a better way of accomplishing this?***
(ps. I could use the conditional split and the derive column into the 50 different table but it was giving me errors that were almost forcing me to use a nvarchar type instead of a varchar type during some of the field transformations.)
At this point I would need to read through each of the outputs sequentially and perform the update, insert or delete into the needed table. Would I have to create 50 script components with an ADO.net recordset adapter to update the tables for each of the outputs? I am hoping you can help come up with a better way to accomplish all of this.
Also if I do need to update the tables with the script component could someone point me to an example of how to programmatically accomplish that. Thanks for any help of suggestions that you may provide as I am feeling kind of stuck.
View 4 Replies
View Related
Apr 18, 2008
Hello,
Is there any way to implement sequence data read.
Note:
source is .csv or flat file file
i want process the records one by one.
please give me the solution ASAP.
Thanks
Thiru
View 4 Replies
View Related
Oct 30, 2005
Hi,
I have more than 1 record for each staff. This is the sample of record,
StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3
50424 Data4 Data5 Data6
50424 Data7 Data8 Data9
80336 Data4 Data5 Data6
Is it possible for me to extract only 1 record for each staff? The result should be like this..
StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3
Any idea? I used DISTINCT in sql statement but it's not working because the record is unique.
Thanx in advance..
View 1 Replies
View Related
Jun 3, 2014
I have a 2table like
create table test1
(col1 identity(1,1),
col2 varchar(20)
)
create table test2
(old int,
new int)
Existing record in test1 table
(1,'test')
Now I want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table
i.e. after insert test1 table will be
1,test
2,test
and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.
If the repeat the process again test2 table will be like
1, test
2,test
3, test
and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.
Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..
I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.
View 2 Replies
View Related
Apr 4, 2007
I am trying to load a file using SSIS that contains records with two different layouts in one data file but in the flat file connection I can only specify one layout and this is causing the records with the second layout to be loaded incorrectly.
The different record layouts can be identified by the first character of the record. Example: If Field begins with "A" then assign one layout; "B" assign second layout.
Has anybody come accross this issue, if so some guidence would be appreciated.
Thanks,
ire_ssis
View 10 Replies
View Related
May 2, 2006
I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.
What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?
Thanks.
View 4 Replies
View Related
Jan 15, 2015
I want to return Order records which are one type and don't have the other type.
The issue is I have Orders with which has 2 distriubtion types .
Example
Order 12345 has Type S and Type X.
Order 67891 has Type S
I only want to return Order 67891 that are s Type and does not have type X
View 1 Replies
View Related
Jan 20, 2007
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach:
The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-
View 3 Replies
View Related
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 6, 2007
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
View 5 Replies
View Related
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Mar 1, 2006
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
View 6 Replies
View Related
Apr 17, 2014
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
View 2 Replies
View Related
Jul 20, 2005
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies
View Related
Jan 14, 2008
Hi Everyone-
i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on
can anyone help?
thanx
Maylo
View 8 Replies
View Related
Apr 23, 2008
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
View 4 Replies
View Related
Jan 31, 2006
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
View 3 Replies
View Related
Feb 25, 2007
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
View 4 Replies
View Related
Mar 18, 2008
Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.
I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.
Thanks. Gary.
View 4 Replies
View Related
Aug 16, 2006
I am trying to update a record in a table based off of criteria of another record in the table.
So suppose I have 2 records
ID owner type
1 5678 past due
2 5678 late
So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
View 5 Replies
View Related
May 26, 2008
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID()
RETURN Date(Today)+Time(Now)+USERNAME
END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
View 10 Replies
View Related
