Data Flow Split
I'm trying to figure out how in a Data Flow Transform I can split some data.
I have data coming through that has a PK (col1) and datetime col (col2).
This data may contain multiple rows for the PK, col1.
I want to be able to take the min datetime for each row for col1 and send down one path and all other rows down another path eg. There are 20 rows in the Data Flow coming through. Col1 has value 1 for first 10 rows, value 2 for remaining 10 rows and there are different values for Col2 for all rows. Take the Min value in Col2, where col1=1 and also the min value in Col2, where col1=2 and send down one path. Send all other rows down another path.
I thought of using the Conditional Split Transform but my Expression knowledge isin't experienced enough (I'm looking into this) and I'm not sure if it can be done.
I also though of using the Multi-Cast Transform and then using the Aggregate function, on each data set. While I can do this easily for the first data flow.
SELECT col1, MIN(col2)
FROM tbl1
GROUP BY col1
which is easily done in the Aggregate transform, the other side of the data flow I can't see how can be done using the Aggregate Transform
SELECT col1
FROM tbl1
WHERE col2 NOT IN (SELECT MIN(col2) FROM tbl1 GROUP BY col1).
Are either methods feasilble or is there another way.
I want to avoid putting this data into temp tables in a SQL database and manipulating the data from there. The data has been extracted from a flat file source.
Any help and ideas welcome.
View Complete Forum Thread with Replies
Related Forum Messages:
Condisional Split In Control Flow?
Hi, I would like to have a decision maker component in Control Flow to decide which Data Flow to run. For example: if myVar=A then run data flowA if myVar=B then run data flowB ...... We have Conditional Split component available in data flow but not in Control Flow. What component in control flow can be used to do the same job that conditional flow is doing in data flow? Or what's a work around for this. Thanks in advance.
View Replies !
How To Pass Parameter Froon Control Flow To Data Flow
Hi, All, I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source. I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source, The SQL Looks like this: select * from ccst_acctsys_account where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >? THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter. Thanks in Advance
View Replies !
SSIS Variables Between Data Flow And Control Flow... How To????
Hi everyone, Primary platform is 64 bit cluster. How to move information allocated in SSIS variables from Data Flow to Control Flow layers?? We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again???? Thanks in advance and regards,
View Replies !
HELP: How Do I Pass Variables From Control Flow To Data Flow
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns. I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox. Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow? Also, how would I update the original source table where source.id = objRects.id? Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered. Thanks, Steve
View Replies !
Is There A Way To Set A Variable In A Data Flow From A SQL Statement (like In Control Flow)
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task. Is there a way to do that using an SQL statement as the source of the value in a data flow? OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
View Replies !
Please Advise: Big Control Flow Or Big Data Flow
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go: A small control flow, with large data flow tasks A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated. Thanks, Ricardo
View Replies !
Lookup Task Data Flow Transformation Causes Data Flow Task To Hang?
Hi, I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM. However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots) http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg The same package works fine against a similar test table with 150k rows. http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table. Any hints,advice would be appreciated.
View Replies !
Need To Save A Value From Data Flow A To Use In Data Flow B
Good morning, all, I am working on importing an Excel workbook, saved as multiple CSV flat files, that has both group level data and related detail row on the same sheet. I have been able to import the group data into a table. As part of the Data Flow task, I want to be able to save the key value for the group, which I will use when I insert the detail rows. My Data Flow has the following components: The flat file with the data, which goes to a derived column transformation to strip out extraneous dashes, which leads to the OLEDB Destination component. I want to save the value as a package level variable, so that I can reference it in another dataflow. Is this possible, and if so, at what point do I save the value? Thanks, Kathryn
View Replies !
OLE DB Source Task In The Second Control Flow Data Task Fails After The Same Task Succeeds In The First Control Flow Data Task
I am trying to run a SSIS package as an agent job. The package has several control flow data tasks. Each of the control flow data tasks uses a OLEDB source task to connect to the same SQL 2005 database(defined by a single connection manager) using SQL server authentication and basically pulls some data. The first control flow data task succeeds, but the second one fails and so he package errors out. My question is why would the second OLEDB task be failing when it uses the same connection manager as the frist one? Here are some facts 1) the package runs fine in BIDS from my development wkstation. 2) the package uses an XML configuration file which specifies the sql server password and connectionstring 3) the package runs on a 64 bit sql server box with SP1. 4) the job is running under sql agent service account. 5) I created the package as well as the agent job. 6) the ole db source is a sql 2005 server. 7) the error message generated by the second task(oledb source) is "The AcquireConnection method call to the connection manager "Source" failed with error code 0xC0202009." I have read about some issues running SQL authenticated ssis packages running under job agent but i wonder if this is related. thankyou, js40
View Replies !
Split Data
My company use SQL server 2005 standard. considering deal with huge data, how if we want to split data (date range yearly or monthly) in order to ease transaction. that's simply for us to use query, but how if we want to split data that can be easily execute by operator (non-admin privilege). Is there any another way?
View Replies !
Data Flow Task Error To Extract Data From Sql Server To Excel
Hi All, I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute. The SQL datable fileds are i) ID - Int ii) RefID iii) txtRemarks - nvarchar(MAX) iv) ddlWaterLevel - nvarchar(50) While executing the tasks, I got the error Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025. After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept. Please do help me out. thanks Sanra
View Replies !
Adding A New Data Column (not Derived) Midway Thru A Data Flow
I need to know what a table's max row Identity is part way thru a data flow. I can't get it at the beginning of the data flow. I need to either (1) add it to the data buffer part way thru or (2) set it into a package variable and then reference the var in a script component. I've not found a way to add a database column to the data buffer without doing a lookup for each row (too slow and not appropriate here) or some goofy oledb source and then merge join into the data buffer on a contrived join. I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up. Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.
View Replies !
Error Writing Data To Same Destination In Single Data Flow
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table: "This operation conflicts with another pending operation on this transaction. The operation failed." The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine. Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction? I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place? Thanks in advance.
View Replies !
How To Split Data Into Two Rows
I have a query that returns a table similar to: State Status Count CA Complete 10 CA Incomplete 200 NC Complete 20 NC Incomplete 205 SC Incomplete 50 What sort of query will allow me to reformat the table into: State Complete Incomplete CA 10 200 NC 20 205 SC NULL 50
View Replies !
Split The Data Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
View Replies !
Split Data In Column
hai all, This is my first question to this forum. here is my situtation: I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query Database : sql server tabel name :job_allocations column naME :technicain code Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician. Based on the technician code user chooses column will be updated if single data will be TC01 if more than one then data will be TC01:TC02:TC03 user can choose any number of techincian for a job MY problem is :How to split tha when there is multiple technician and calculate cost for the job Ineed it in single excecution query Table structure job_allocation table jobcardn_fk Technician_code jc01 TC01 jc02 Tco1:Tco2:Tc03...... I need it in jobcardno_fk TEchnician_code jco1 Tc01 jco2 Tc01 jco2 TC02 jc02 Tc03 TKs ands Regards Diwakar.R
View Replies !
Handle Tasks In &&"Control Flow&&" Tab From &&"Data Flow&&" Tab
Dear All! My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database. Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any Thanks
View Replies !
Data Flow: Converting Data In Multiple Columns
Hi, I'm just wondering what's the best approach in Data Flow to convert the following input file format: Date, Code1, Value1, Code2, Value2 1-Jan-2006, abc1, 20.00, xyz3, 35.00 2-Jan-2006, abc1, 30.00, xyz5, 6.30 into the following output format (to be loaded into a SQL DB): Date, Code, Value 1-Jan-2006, abc1, 20.00 1-Jan-2006, xyz3, 35.00 2-Jan-2006, abc1, 30.00 2-Jan-2006, xyz5, 6.30 I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
View Replies !
Data Reader Source In Data Flow Problem
hi all, i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following: how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example: select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it. please help, tomer
View Replies !
Exporting Data From A Merge Join From One Data Flow To Another
Hi, Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
View Replies !
Set The Data Source Of Data Flow From External Application (C#)
I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!
View Replies !
Data Unexpectedly Changing In Data Flow Task
I have a fairly simple data flow task that loads data from one table (OLE DB Source) into another table (OLE DB Destination). The data type for one of the pairs of columns is nVarChar(120) and it contains version information that looks like a decimal. When I run the export, the destination has a trailing zero added after the decimal point as if it were a numeric column which invalidates our comparisons (string 1.0 is not the same as string 1.00). There is no cast or convert done to this column, it is a straight copy. Any ideas what could be causing this or how to fix?
View Replies !
One Data Flow Task And Multiple Data Flows
I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately. The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible) Thanks
View Replies !
T-SQL To Split Data From One Table Into Two Tables?
What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here. Doing this via cursor is pretty straightforward, but is there a comparable set-based solution? Here are sample create table commands. Obviously, the example below is simplified for discussion purposes. -- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records. CREATE TABLE OriginalSingleTableRecords ( ID INT IDENTITY (1, 1) NOT NULL, ColumnA VARCHAR(100) NOT NULL, ColumnB VARCHAR(100) NOT NULL, CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID) ) CREATE TABLE TargetParentRecords ( ParentID INT IDENTITY (1, 1) NOT NULL, ColumnA VARCHAR(100) NOT NULL, CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID) ) -- Each row in this table must link to a TargetParentRecords row CREATE TABLE TargetChildRecords ( ID INT IDENTITY (1, 1) NOT NULL, ParentID INT NOT NULL, -- References TargetParentRecords.ParentID ColumnB VARCHAR(100) NOT NULL, CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID) )
View Replies !
Using Condation Split To Update Data
hi all i using SSIS to update data on tables based on another tables as follow start connect to DS --> using Lookup to located the key fields on the 2 tables --> on error start insert into the second table else if the recorde is already exist start update my problem is Performance --source table rows count almost 60 million --distination table almost 3 million my package execute from 2 days and still working till now updated 122,000 and insert 4 million kindly i need support thanks & regards
View Replies !
Split Data Into Two Column Table
Hello all, Little layout question. Assume my dataset returns the following data: A B C D E How can I present this data in a table (or list, or matrix) splitted into two columns: A B C D E Any idea will be very appreciated! Thanks a lot! TG
View Replies !
How To Split Data From The MS SQL 2000 Database?
Hi All, We are working on a project, (C#.Net 2003, MS SQL 2000) where database is growing as 2 to 3 GB per day (Scanned Documents are storing in Database). There is only one table in the database. Currently database size is grown upto 200 GB. For safety reason we are planning to split the database accoring to one key field in the table say Book_no. Please guide/suggest me how to split the database now on the SQL query like "SELECT * FROM master_records WHERE book_no=1" And later how to merge/combine all these splitted database into one. I thought of using "Data Export" and later "Import with Append" but don't know whethere it will effective or not? Any method or tool available to Split the database table and later combine them to make again a single one? Please help me, its urgent. Thanks in advance.
View Replies !
Importing Split Data Into Table
Hi, I have Data split into 3 text files with 3 fields repeated in each to link then (key). I want to import this data into one table. I used DTS to create 3 tables with the data. Now i want to combine the 3 tables into only one (that i already created). How can i do this? Note: the field names in the source tables are different from the destination table. Thanks Guy
View Replies !
Split A Single Column Data In To 2 Columns
Hi This is probably a very basic question for most people in this group. How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure. Here is an example of what i want to acheive FName John?Doe FName LName John Doe thanks for the help prit
View Replies !
Split Column Data Into Multiple Lines
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below . "FTW*Christopher,Lawson FTW*Bradley,James" Please let me know how can I acheive this?
View Replies !
Scrubbing Data In A Data Flow Task
I can only assume that I either did something very stupid, the examples are completely wrong, or I don't have the slightest clue what it is that I'm doing. I'm trying to do one of those incredibly complicated things in SSIS that was a brain-dead, point click operation in DTS. I want to strip characters out of an input stream from a flat file source before loading them into a table. I've defined my data flow task. I have my flat file source properly defined. I have my SQL Server destination properly defined. For the life of me, I can't figure out how to scrub the data that I want. My input is a csv file that has 22 columns of data. Column 22 contains data and if data was not present, there is a - inserted. So, I want to strip any - from the input before it goes into the column. So, I grabbed a derived column task and drug it out on the surface of the designer. I then set the derived column name to Column 22, the derived column to 'Replace Column 22', and tried the following in the expression section: REPLACE(Column 22, "-", "" ) REPLACE( (DT_WSTR, 50) Column 22, "-", "" ) Both of these threw some really nasty error messages when I clicked the OK button. The really irritating part is that I exactly followed the examples in BOL.REPLACE(Product, "Bike","")REPLACE((DT_WSTR,8)DaysToManufacture,"6","5") I know I'm not dreaming this, but it absolutely refuses to accept my expression.
View Replies !
Using An ODBC Data Source In A Data Flow
Okay, this should be really simple but I don't get it. How do I use an ODBC data source in an SSIS data flow task? When I look at Data Flow Sources I see the following options: Pointer DataReader Source Excel Source Flat File Source OLE DB Source Raw File Source XML Source Which one do I use if I need to get the data from a connection manager that is ODBC based? The IBM OLEDB driver for the AS400 doesn't work correctly so I HAVE to use an ODBC driver to connect to an AS400 data source. Thanks in advance for any info.
View Replies !
Pass Data Between Two Data Flow Tasks
Hi Guys, I have yet another question. How can i pass data b/w 2 data flow tasks? I'm trying to get some data from one sql server and then I want to pass this whole bunch of data to another data flow task which is going to get some data from second sql server. I would probably combine them using union all and then save that as a transaction in a third sql server? Information regarding how to pass the data with some detailed discussion would be fine with me. thanks Gemma
View Replies !
Retrieving Data From A DB Based On Output Of A Conditional Split
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need. Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing. Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command. How can I do this? Thank you! Nolan
View Replies !
Deadlock Problem? 3 Way Conditional Split Of Data From One Table To Another Never Completes
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete. Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
View Replies !
Data Flow
Is there a way to extract data from a source and insert these data directly into destination without using an intermediate step like storing data in a text or staging table? I am trying to use the data flow task: Source -> Destination. The Data Access Mode for both the source and destination is from a variable name. Thanks in advance. Andrew
View Replies !
Help With Data Flow
I would like to use Integration Services to update data in my datawarehouse. I have a table called "AgentStats" that stores archived data from the past 3 years. I would like to import the current year's data from the production server into the same table in my datawarehouse and have my ETL update only the current year's day on a daily basis. The current year's data is constantly updated in the datasource, so I achive the data at the end of the year. Any ideas how I can accomplish this? Thank You -Sam
View Replies !
Data Flow
I am designing a data flow archieture.I need the suggestions. What are the advantages of storing data of the flat files to sqlserver.
View Replies !
Split DB - Static Controls Versus User Data - Feedback On Plan
In my prototype, I've got i) the (relatively) static control files (various control values) and ii) the user datain the same DB (SQL Server Express). Both types of files are entirely accessed through table adapters. I'm thinking I'd like to split the data into two databases, one for just the static control files, and the other for the user data. Then split the app into two separate apps, one for user data, one for just the control tables. I'm thinking this will be more secure, easier to maintain and rebuild if something fails, other reasons (...?) I'm hoping to get some general feedback on the reasonability of doing this, and on my general approach. My first approximation of a plan to do this would be:0) lot's of backups first and during!A) Copy the current DB, rename as "Control-something, delete the "user" tables from it, then delete the control tables from the original "Data" DB B) similarly copy, rename, split the app files into "control" and "user" appsC) in the both of the resulting apps 1) create a new connection string to the "new" control DB 2) Edit the tableadapters as necessary to use the new connection string D) Figure out where there needs to be any "cross over" links between the two apps (dunno right now, users won't touch app controls, though maybe some administrators would....) E) Plan to deploy both DB's and both apps as appropriate, with separate access URL's for the two different apps. So the questions are 0) Any real advantages to doing this, for security, maintenance, etc?1) Is this a "commonly used" approach, or do most people just keep the data and controls in one DB?2) Any issues with one app / multiple DB;s (it's going to be a single server app for the first release.)3) Any issues with simultaneously accessing one DB from two apps?4) Are there any big missing pieces in this conversion plan? Any feedback on this would be appreciated. Thanks!
View Replies !
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 Replies !
Help With SSIS Data Flow
To keep this simple, I have a control flow that loops through files in a folder. Within that loop I have a data flow. In my data flow I read in the file as a whole to get some counts of header rows, data rows, and total file size and store that in a db using various components. The last component in my data flow so far is a script component which I use to validate something so that I have set up 2 output paths, 1 if the validation passes and 1 if it errors out. For the error, I store the info in the db. For the row that passes, what I need to do is now go back to the origional file and instead of loading it as a whole, I need to now load it separating the data into columns. I cannot figure out how to do this. I was going to use another file source to reload but it will not let me connect the output of the script component to it. Does anyone have any ideas on how I can now go back to the orig file on disk and reload the data? or is there another suggestion? Thanks a bunch in advance!!!
View Replies !
Data Flow Based On A DSV
Hi, I've got this feeling that something is missing in Integration Services. You can add a DSV in any Integration Services project, but you can't use it inside a data flow. Can someone please tell me why this is? Let me explain why i want this: In Dynamics AX you've can make perspectives. These perspectives are a combined set of tables. From these perspectives you can automatically deploy these as Report Models. The Reporting Services generates a DSV for you, based not only on physical tables but also on Business logic. So it generates the linking between the tables (based upon application logic), derrived enumerated tables, referred tables, etc.. So these generated DSV's are great to get a quick startup. So repeating my question. Why can't i select a DSV as source? What is the function of a DSV inside Integration Services? Hoping to receive an answer for this question. Kind regards, Johan Blad
View Replies !
|
TRACKER
