August 12, 2008 at 10:58 am
This problem might not directly relate to SSIS.
But this is the part of the same problem that I have posted here in topic "IS IT Possible to delete files if size is 0 Kb".
I have a OLEDB SOurce and I am using SQL command query to get my data from Source. The data comes from two diff tables based on condition. The query is below...Then this data moves to several conditional splits to make 7 diff flat files.
The issue here is I have to Update one column in each table ( HDR and DTL tbs) "CLM_HDR_STA_CD]" this is HDr column
and CLM_LN_STA_CD"-- this is dtl line status.
Once I select this query and write to multiple files I have to go and update the status for each Hdr row and its associated line items to 'T' in dtls tables
( If u see the query , my query depends on S status of line items, not S status of Hdr row)
I can think of several options like using Sql task to update columns based on same serach criteria i have in my Query. But again I am running the same statement 3 times -- one to find my data, 2nd to update hdr and 3rd to update detail. I believ another would be to use OLEDB command trnasform...
ANY OTHER WAYS??????????????
SELECT
h.[EDI_CLM_ID]
,h.[EDI_CTL_TS]
,h.[EDI_CLM_SEQ_NBR]
,h.[VEND_NM]
,h.[REC_TYP_CD]
,h.[BILG_NPI_ID]
,h.[BILG_TAX_ID]
,h.[BILG_PROV_ZIP_CD]
,h.[VEND_CLM_ID]
,h.[EDI_CLM_ID] as [REF_CLM_NO]
,h.[PAT_FST_NM]
,h.[PAT_MID_NM]
,h.[PAT_LST_NM]
,h.[PAT_HCSC_ID]
,h.[PAT_MCAID_ID]
,h.[PAT_GRP_ID]
,h.[PAT_DOB]
,h.[BILL_AMT]
,h.[ADJ_IND]
,h.[RNDRG_NPI_ID]
,h.[RNDRG_TAXNMY_CD]
,h.[ENCTR_TYP_CD]
,h.[PD_AMT]
,d.[CLM_LN_NBR]
,d.[PROC_CD]
,d.[PROC_MDFYR_1_CD]
,d.[PROC_MDFYR_2_CD]
,d.[PROC_MDFYR_3_CD]
,d.[PROC_MDFYR_4_CD]
,d.[SVC_FR_DT]
,d.[SVC_TO_DT]
FROM dbo.CLM_HDR h
INNER JOIN
CLM_LN d
ON (h.CLM_KEY = d.CLM_KEY)
WHERE NOT EXISTS (SELECT 1 FROM CLM_LN d1
WHERE d1.CLM_KEY = d.CLM_KEY AND
d1.[CLM_LN_STA_CD] <> 'S')
SET NOCOUNT OFF
August 12, 2008 at 11:33 am
Yes. you are correct. You need to use the OLEDB command to update the status code.
You can use "Multicast" component to give same SELECT statement source to UPDATE statements.
Using "Multicast" you can give one input to multiple outputs.
I have attached 2 files. depending on your requirement you can select either of these two.
Hope this helps 😉
August 12, 2008 at 11:51 am
Thanks, Sara....!!!!
Your Second one looks to fit my scenerio.
Once I Select my Source , then I can have three multicast----
1. to writre to 7 diff files.
2. one to update my hdr row and
3 one to update my dtl row..
Let me see if I will be able to do it or not ( i hav enever used OLEDB command so far)
I will try out and let you knwo th reulsts............
Thanks Again
August 12, 2008 at 11:58 am
Hey Looks like I have to Join my Multicast again ,.... becoz when I have OLEDB command its expecting the destination ................
So i have to join again I belive..........
ANy one out there got better approacj............?
Thanks
August 12, 2008 at 2:09 pm
SQL Command would work but is slow if you on a large data set.
Why not push them into a temp table and do a join. Will be far faster on a large data set
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 2:28 pm
"OLEDB Command" does not require destination.
I have used it in my all packages to updates the records.
If you are getting error can u please post the error or snapshot of your package.
Thanks !!
August 12, 2008 at 2:51 pm
Why not do it in reverse?
Try this:
Update My Table
OUTPUT INTO #MyTempTable
Where
bla bla bla
Select * From #MyTempTable
You run the update, save the affected rows out to a temp table.
Dump the rows from the temp table into the data flow and then onto your text files.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 2:56 pm
SOrry I took out all the OLEDB command and multicast, since it was giving me all kind of Errors--
Here is the package for you -------
Now Looks like it is hard to use update in this OLEDB command ( or may be i don't know).
I was thinking to update hdr first through sql task and then update dtl based on the hdr information....., using Sql task
Give me your email adress i will send you the package and if you got time plz help me here out.
FOrget to mention few things...
the columns i am updating in the hdr and dtl table is not selected in the SQL Command source and also i have to update another column with getdate that is also not included in the select query list..
Thanks
August 12, 2008 at 3:09 pm
If possible send me structure of both the tables I will try it out.
August 12, 2008 at 4:06 pm
provide me thee password for package.
I am unable to open it as it is password protected
August 12, 2008 at 6:05 pm
Check your mail. I have sent you the updates
August 12, 2008 at 11:46 pm
What about the rest of the poor folk who were hoping to see the solution?
If you going to offer private help, do it via PM.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 13, 2008 at 7:19 am
DOn't worrry Crispin , I will defenately post it , Once I am Done with it...
Just give me some time...................
Thanks
August 13, 2008 at 7:55 am
Here What Saral.... Has Done is ................
He added one more column so that he can use that column in is update statement for Dtl tbls. He HAs Two OLEDB Command Transform, one to update HDR tbl and one to UPDATE DTL tbl. There is a muticast which goes into 3 diff wasy one to do my conditional splits and one to update Hdr and one to update Dtl.
He has some ideas about doing the split in more efficient way.., But i belive I am gonna leave it as it is...........may be i DOn't want more complecity............
I have attach the Looks for you
ANd this is what he has in the OLEDB command
UPDATE
dbo.Clm_HDR
SET
CLM_HDR_STA_CD = 'T', SNT_TO_EDI_TS = getdate()
WHERE
EDI_CLM_ID = ?
--------------------------------
UPDATE
dbo.Clm_Ln
SET
CLM_LN_STA_CD = 'T'
WHERE
CLM_KEY = ?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply