August 17, 2015 at 3:15 pm
I have a query where it has atleast 4-5 temporary tables and finally data is selected from that temporary table. The data from which i pull (source) is in different server and the data where i need to push (destination) is in different server.
The source is a SQL script , and for this source server , i have only read access. So ,I cannot write data to table or create any stored procedure. So when I try to past query in "execute sql task" the parameters ( start date and end date) are recognized , but i cannot pull the data to table as the destination table is in different server.
When I use" dataflow task " , copy the script and paste it , the parameters are not recognized. Can anyone help me to understand why the parameters are not recognized in data flow task?
Is there any solution to this issue?
August 18, 2015 at 10:54 am
You need to do this using data flow. can is see the query you are trying to use for the source query. Where are the parameters stored to pass to the query? (in A variable)
August 18, 2015 at 12:18 pm
Here is the query format. I could not get you orginal query but this is the format of it.
DECLARE @ReportStartDate DATE = '07/01/2015'
DECLARE @ReportEndDate DATE = '07/01/2015'
--staging tenp table creation
IF OBJECT_ID('tempdb..#FinalOutPut') IS NOT NULL
BEGIN
DROP TABLE #FinalOutPut
END
CREATE TABLE #FinalOutPut
(
Asdfg VARCHAR(60)
,M_id VARCHAR(100)
,GP_id INT
,C_id int
,Dt_modified DATETIME
,UID VARCHAR(50)
)
IF OBJECT_ID('tempdb..#EC') IS NOT NULL
BEGIN
DROP TABLE #EC
END
SELECT DISTINCT
M_id,
C_GID,
INTO#EC
FROME_COv WITH(NOLOCK)
UPDATE E
SET E.plan_gid = E_PARENT.plan__gid
FROM #EC E
JOIN #EC E_PARENT
ON E.C_GID = E_PARENT.C_GID
AND E_PARENT.parent_gid = E_PARENT.C_GID
ANDE.Dt_modified BETWEEN E_PARENT.Dt_modified AND E_PARENT.last_touch
AND E.plan_gid= -1
CREATE CLUSTERED INDEX IX_EC ON #EC(C_GID,parent_gid,effective_date,termination_date,default_lob)
--transactions
--DOB Change Transaction starts here
;WITH Chng_Seq
AS (
SELECT *
FROM DBO.Contacts C WITH (NOLOCK)
WHERE CAST(C.last_touch AS DATE) BETWEEN @ReportStartDate AND @ReportEndDate
)
INSERT INTO #FinalOutPut
SELECT *
FROM A
JOIN B ON B.Rownum = A.Rownum + 1
WHERE A.birth_date <> B.birth_date;
;WITH Chng_Seq
AS (
SELECT *
FROM DBO.Contacts C WITH (NOLOCK)
WHERE CAST(C.last_touch AS DATE) BETWEEN @ReportStartDate AND @ReportEndDate
)
INSERT INTO #FinalOutPut
SELECT *
FROM C
JOIN D ON B.Rownum = A.Rownum + 1
WHERE C.birth_date <> D.birth_date;
SELECT DISTINCT
*
INTO #Summarytable
FROM #FinalOutPut A
SELECT *
FROM #Summarytable
WHERE IState = 'IL'
SELECT *
FROM #Summarytable
WHERE IState = 'TX'
SELECT *
FROM #Summarytable
WHERE IState = 'OK'
SELECT *
FROM #Summarytable
WHERE IState = 'MT'
SELECT *
FROM #Summarytable
WHERE IState = 'NM'
August 18, 2015 at 12:20 pm
The above query pulls data from another source , where i can only read data not write. I need to run this query and pull the data and insert it into table on other server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply