DSXchange

What I meant when I said are the key columns always the same, was Are the key columns identifiable, and are they same in each target table. Depending on your requirements you should be able to use a sequential file stage to read in the data. Turn RCP on and set First row is Column headers. This will...

You can have a reject link on the lookup. Just set your lookup to Fail on reject. Any input columns which fail will be output on the reject link. Think of the reject link as lookup value not found rather that a reject. From the reject link you can process your records. Output the rows which are foun...

You could probably do it with 2 sql select statements and a lookup. SQL Statement 1. Select * from table. SQL Statement 2: SELECT * FROM (SELECT column1, column2, COUNT(*) over(partition BY column1) col1cnt, COUNT(*) over(partition BY column2) col2cnt FROM (SELECT DISTINCT column1, column2 FROM test...

Any extra column shouldn't matter because you are processing each column individually. However the fact that you have this extra column with what look like DB actions could mean there were other options, and throws up other questions. But we will progress with what we have for now. If you have outpu...

Yes CDC could be of use here, but for the actual circumstance it would be like using a sledgehammer to crack a nut.

If your source is already coming in as a 2 column input of header values and data values ie Headers Data ABC 123 DEF 234 XYZ 345 GHI 333 JKL 222 MNO 666 ABC 789 MNO 555 then just follow the logic that I provided from that point . There is no need to pivot the data. From this point you can create an ...

Is there any logic to the way the data is received? From you example it looks like you receive 3 columns at time? And the rows arrive as header row, data row, header row, data row? If that is the case, I would try to the the data into 2 columns, one with the header row value, and one with the data v...

Thanks for the input Craig I too was sceptical that RCP could run for an 'update'. If you actually choose any sort of update it does not work. DS will kick up an error before you even run the job. However it seems that with the ODBC connector it utilises the target metadata to determine what the key...

Yes Craig, everything is still RCP.

FYI: We have done further investigations and tests, and have found that the issue is begin caused by the Primary Key name on the tables. If we change the primary key name the process seems to work. No idea as to why this would be the case. I am thinking that it might be the actual ODBC connector som...

The 'code' that you require will depend on a number of factors, that will in the end effect the job design that you decide upon. Factors that will influence this can include: Input type: eg DB or File Operating Systems, environment performance, data size etc eg if input is an Oracle Table, then you ...

How could I possibly know what your business rules are?

I don't suppose that you have saved the table definitions of the datasets and imported them into the DataSet stage each time a job was created? If yes then you can just do a where used on the table definition. Otherwise, it would most likely be some sort of export to DSX and search for the jobs usin...

Depending on how difficult it is to identify the header and trailer records in your input table, i would just have the 3 jobs required, each one filtering the input from the source for the appropriate records. eg i. Job reads the source table for header records ii. Job reads the source table for tra...

DBA of sorts = not a DBA but knows a lot more about MSSQL than I do (mind you this is not difficult). I too am thrown as to why a copy of the table structure would work, but creating it in a new DB doesn't. It suggests that there is something happening at a higher level than the DB which is acting o...