sql server dts: unable to define transformations with a dts using a stored procedure (SQL Server 2000) -- sql server dts

unable to define transformations with a dts using a stored procedure (SQL Server 2000)

Jim Underwood
10/18/2007 4:59:30 PM

sql server dts: I am attempting to write a file to disk, based on the results of a stored
procedure, but the transformations will not work. If anyone has any advice
on how to make this work, I would greatly apreciate it.

I have a Microsoft OLEDB Provider for SQL Server connection and a Texst File
(Destination) connection, with a Transform data task to retrieve data from a
stored procedure and output it to the file. The transform data task uses a
SQL Query (actually a stored Proc) to get the data.

I successfully created 2 DTS packages to do this, but now I need to change
the stored procedure to contain an extra column. I am unable to define my
transformations for the changed columns, and DTS will not read the columns
that the stored proc is returning.

When I created the DTS package it would not acknowledge the columns in my
stored procedure until I went into "Build Query" and actually executed the
stored procedure there. Once I did this, however, the destination and
transformation tabs populated automatically.

This is no longer working. I have tried clicking on define columns on the
destination tab, and populating from source, but it does nothing.

The exact commands I have tried to use to execute my stored procedure are as
follows:

usp_HEIDIS_TDL '20060101', '20080101', '20050101', '20080101'
exec usp_HEIDIS_TDL '20060101', '20080101', '20050101', '20080101'

Both of these work fine in QA, and both return results under Preview or
Build Query inside the transform data properties.

Below is my stored procedure code, in case anyone sees anythign wrong with
it. I am using the temp table to avoid having the query run for hours.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure usp_HEIDIS_TDL
(
@cvgStart datetime,
@cvgEnd datetime,
@DOSStart datetime,
@DOSEnd datetime

)
as
set nocount on

select
PATIENT.PAT_ID into #TempPatients
FROM PATIENT PATIENT
INNER JOIN COVERAGE_MEM_LIST COVERAGE_MEM_LIST
ON PATIENT.PAT_ID=COVERAGE_MEM_LIST.PAT_ID
INNER JOIN COVERAGE COVERAGE
ON COVERAGE_MEM_LIST.COVERAGE_ID=COVERAGE.COVERAGE_ID
WHERE 1=1
AND COVERAGE.CVG_EFF_DT < @cvgEnd
AND
(
COVERAGE.CVG_TERM_DT >= @cvgStart
or COVERAGE.CVG_TERM_DT is null
)
and COVERAGE.PAYOR_ID in (select distinct payor_id
from CLARITY_EPM
where PAYOR_NAME like 'FCHP%'
)
and patient.pat_id = 'Z346228'
;

select top 1
tdl.int_pat_id
, ser.prov_name
, spec.name as specialty
, tdl.proc_id
, tdl.orig_service_date
, COVERAGE_MEM_LIST.MEM_NUMBER
, EAP.PROC_NAME
, PATIENT.PAT_FIRST_NAME
, PATIENT.PAT_LAST_NAME
, PATIENT.BIRTH_DATE
, COVERAGE.PAYOR_ID
, DX1.ICD9_CODE as ICD9_CODE1
, DX2.ICD9_CODE as ICD9_CODE2
, DX3.ICD9_CODE as ICD9_CODE3
, DX4.ICD9_CODE as ICD9_CODE4
, DX5.ICD9_CODE as ICD9_CODE5
, DX6.ICD9_CODE as ICD9_CODE6
FROM clarity_tdl_tran as TDL
INNER JOIN CLARITY_EAP as EAP
ON TDL.PROC_ID = EAP.PROC_ID
INNER JOIN PATIENT PATIENT
ON TDL.INT_PAT_ID=PATIENT.PAT_ID
inner join clarity_ser ser
ON ser.prov_id = tdl.performing_prov_id
inner join dbo.CLARITY_SER_SPEC serspec
on serspec.prov_id = tdl.performing_prov_id
and line = 1
inner join dbo.ZC_SPECIALTY spec
on serspec.specialty_c = spec.specialty_c
INNER JOIN COVERAGE_MEM_LIST COVERAGE_MEM_LIST
ON PATIENT.PAT_ID=COVERAGE_MEM_LIST.PAT_ID
INNER JOIN COVERAGE COVERAGE
ON COVERAGE_MEM_LIST.COVERAGE_ID=COVERAGE.COVERAGE_ID
left outer JOIN dbo.CLARITY_EDG DX1
ON TDL.DX_ONE_ID = DX1.DX_ID
left outer JOIN CLARITY_EDG DX2
ON TDL.DX_TWO_ID = DX2.DX_ID
left outer JOIN CLARITY_EDG DX3
ON TDL.DX_THREE_ID = DX3.DX_ID
left outer JOIN CLARITY_EDG DX4
ON TDL.DX_FOUR_ID = DX4.DX_ID
left outer JOIN CLARITY_EDG DX5
ON TDL.DX_FIVE_ID = DX5.DX_ID
left outer JOIN CLARITY_EDG DX6
ON TDL.DX_SIX_ID = DX6.DX_ID
WHERE 1=1
AND tdl.orig_service_date >= COVERAGE.CVG_EFF_DT
AND (tdl.orig_service_date <= COVERAGE.CVG_TERM_DT or COVERAGE.CVG_TERM_DT
is null)
and tdl.orig_service_date >= @DOSStart
and tdl.orig_service_date < @DOSEnd
and TDL.INT_PAT_ID in
(select PAT_ID FROM #TempPatients)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Re: unable to define transformations with a dts using a stored procedure (SQL Server 2000)

Jim Underwood
10/19/2007 10:36:01 AM

I found a solution for this problem, or rather a way to manually update the
transformations.

It may be a bug related to having more than 256 characters in a row, where
the OLEDB text connection defaults to 256 character limit.

Here is what I did to correct it, all manual.

I opened the package and selected "Disconnected Edit" from the "Package"
menu. Under Connections, Text File (Destination), OLE DB Properties, I
updated the following properties:
Column Lengths (specified the length of all columns in the order returned by
the stored proc)
Column Names (specified the names of all columns in the order returned by
the stored proc)
Number of Column (specified the number of columns returned by the stored
proc)
Text Qualifier Col Mask: 0=no, 1=yes, eg. 0101 (added and updated the bits
for the columns, in the order they are returned by the stored proc)
Max characters per delimited column (changed from 255 to 8000)
Blob Col Mask: 0=no, 1=yes, eg. 0101 (added and updated the bits for the
columns, in the order they are returned by the stored proc)

There may have been a better way to correct this, other than changing all of
the values manually, so that the wizard would work, but I could not find
one. It took several passes before I found all of the settings that needed
to be changed, and if one was missed EM would crash when I attempted to look
at the transformations.

[quoted text, click to view]