Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to Execute .SQL(stored procedure file) through

Author  Topic 

Arunraj
Starting Member

18 Posts
Posted - 2014-10-28 : 10:38:29
Hi Friends,
I have the SQL script in which the concept is Datas must send to the respective parent and Child table.And if there is any Error occurs those rows will go to the Error Log table.We have used the (:r)path to call the log table in between the scripts I can run this script successfully through SSMS, But i dont know how to execute this through SSIS .Please suggest me how to do this Below is the script i need to Execute.


:r "C:\Clients\BlackBook\BlackBookMarketing\SQLScripts\SETENVIRONMENT.sql"
-- ============---=========================================================SERVICE_APPOINTMENT_CURSUR===================================================================
USE [IconicMarketing]
GO

DECLARE
@FileType varchar (50),
@ACDealerID varchar (50),
@ClientDealerID varchar (50),
@DMSType varchar (50),
@AppointmentNumber Varchar(20),
@RONumber varchar (258),
@CustomerName varchar (50),
@CustomerHomePhone varchar(100),
@CustomerEmailAddress varchar (50),
@AppointmentDate date,
@AppointmentTime date,
@VehicleYear varchar(100),
@VehicleMake varchar (50),
@VehicleModel varchar (50),
@VehicleVIN varchar (50),
@ServiceAdvisorNumber varchar(200),
@OperationCode varchar (50),
@ComplaintStatement varchar (302),
@Comments varchar (max),
@CustomerFirstName varchar (50),
@CustomerLastName varchar (50),
@CustomerAddress varchar (50),
@CustomerCity varchar (50),
@CustomerState varchar (50),
@CustomerZip varchar (12),
@CustomerCellPhone varchar (15),
@CustomerNumber varchar (15),
@CustomerWorkPhone varchar (15),
@Department varchar (50),
@CASS_STD_LINE1 varchar (50),
@CASS_STD_LINE2 varchar (50),
@CASS_STD_CITY varchar (50),
@CASS_STD_STATE varchar (50),
@CASS_STD_ZIP bigint,
@CASS_STD_ZIP4 varchar (255),
@CASS_STD_DPBC varchar (255),
@CASS_STD_CHKDGT varchar (255),
@CASS_STD_CART varchar (50),
@CASS_STD_LOT varchar (255),
@CASS_STD_LOTORD varchar (50),
@CASS_STD_URB varchar (255),
@CASS_STD_FIPS varchar (255),
@CASS_STD_EWS varchar (50),
@CASS_STD_LACS varchar (255),
@CASS_STD_ZIPMOV bigint,
@CASS_STD_Z4LOM varchar (50),
@CASS_STD_NDIAPT varchar (255),
@CASS_STD_NDIRR varchar (255),
@CASS_STD_LACSRT varchar(255),
@CASS_STD_ERROR_CD varchar (10),
@NCOA_AC_ID bigint,
@myerror Varchar(500),
@ServiceAppointID int,
@errornumber int,
@errorseverity varchar(500),
@errorstate int,
@errorprocedure varchar(500),
@errorline varchar(50),
@errormessage varchar(1000),
@errortable varchar(50),
@SAI_Id INT,
@createddate datetime;

DECLARE SERVICE_APPOINTMENT_Cursor CURSOR FOR
SELECT * from FLATFILE_SERVICE_APPOINTMENT order by createddate;

OPEN SERVICE_APPOINTMENT_Cursor

FETCH NEXT FROM SERVICE_APPOINTMENT_Cursor
INTO @FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@createddate
;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VehicleVIN ;
IF Exists (Select 1 From SERVICE_APPOINTMENT Where IconicDealerID = @ClientDealerID And AppointmentNumber = @AppointmentNumber
And CustomerNumber = @CustomerNumber AND AppointmentDate = @AppointmentDate )
Begin
Select @SAI_Id = ID From SERVICE_APPOINTMENT Where IconicDealerID = @ClientDealerID And AppointmentNumber = @AppointmentNumber
And CustomerNumber = @CustomerNumber AND AppointmentDate = @AppointmentDate

Delete From [SERVICE_APPOINTMENT_CUSTOMER]
Where ServiceAppointID = @SAI_Id

Delete From [dbo].[SERVICE_APPOINTMENT_DETAILS]
Where ServiceAppointID = @SAI_Id

Delete From [dbo].[SERVICE_APPOINTMENT_VEHICLE]
Where ServiceAppointID = @SAI_Id

Delete From SERVICE_APPOINTMENT
Where ID = @SAI_Id

End

--================================================================================================================================================
-- ***************************************************** INSERT INTO SERVICE_APPOINTMENT********************************************************
--================================================================================================================================================
BEGIN TRY
INSERT INTO SERVICE_APPOINTMENT
(
IconicDealerID,
AppointmentNumber,
RONumber,
Customernumber,
DMSType,
AppointmentDate

)
VALUES (
@ClientDealerID,
@AppointmentNumber,
@RONumber,
@Customernumber,
@DMSType,
@AppointmentDate
);

END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE(),
@errortable = 'SERVICE_APPOINTMENT',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();

--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[LOG_SERVICE_APPOINTMENT_ERROR]
(
FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
AppointmentNumber ,
RONumber ,
CustomerName ,
CustomerHomePhone ,
CustomerEmailAddress ,
AppointmentDate ,
AppointmentTime ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleVIN ,
ServiceAdvisorNumber ,
OperationCode,
ComplaintStatement ,
Comments ,
CustomerFirstName ,
CustomerLastName ,
CustomerAddress ,
CustomerCity ,
CustomerState ,
CustomerZip ,
CustomerCellPhone ,
CustomerNumber ,
CustomerWorkPhone ,
Department ,
CASS_STD_LINE1 ,
CASS_STD_LINE2 ,
CASS_STD_CITY ,
CASS_STD_STATE ,
CASS_STD_ZIP ,
CASS_STD_ZIP4 ,
CASS_STD_DPBC ,
CASS_STD_CHKDGT ,
CASS_STD_CART ,
CASS_STD_LOT ,
CASS_STD_LOTORD ,
CASS_STD_URB ,
CASS_STD_FIPS ,
CASS_STD_EWS ,
CASS_STD_LACS ,
CASS_STD_ZIPMOV ,
CASS_STD_Z4LOM ,
CASS_STD_NDIAPT ,
CASS_STD_NDIRR ,
CASS_STD_LACSRT ,
CASS_STD_ERROR_CD ,
NCOA_AC_ID ,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable)
;

END CATCH

--PRINT @errornumber;
--PRINT @errorseverity;
--PRINT @errorprocedure;
--PRINT @errorline;
--PRINT @errormessage;
--PRINT @errorstate;

set @myerror = @@ERROR;

-- This PRINT statement prints 'Error = 0' because
-- @@ERROR is reset in the IF statement above.
PRINT N'Error = ' + @myerror;

set @ServiceAppointID = scope_identity();
PRINT @ServiceAppointID;

--================================================================================================================================================
-- *********************************************Insert into SERVICE_APPOINTMENT_CUSTOMER Table****************************************************************
--================================================================================================================================================

BEGIN TRY

INSERT INTO SERVICE_APPOINTMENT_CUSTOMER
(
ServiceAppointID,
CustomerName,
CustomerHomePhone,
CustomerEmailAddress,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity,
CustomerState,
CustomerZip,
CustomerCellPhone,
CustomerNumber,
CustomerWorkPhone,
CASS_STD_LINE1,
CASS_STD_LINE2,
CASS_STD_CITY,
CASS_STD_STATE,
CASS_STD_ZIP,
CASS_STD_ZIP4,
CASS_STD_DPBC,
CASS_STD_CHKDGT,
CASS_STD_CART,
CASS_STD_LOT,
CASS_STD_LOTORD,
CASS_STD_URB,
CASS_STD_FIPS,
CASS_STD_EWS,
CASS_STD_LACS,
CASS_STD_ZIPMOV,
CASS_STD_Z4LOM,
CASS_STD_NDIAPT,
CASS_STD_NDIRR,
CASS_STD_LACSRT,
CASS_STD_ERROR_CD,
NCOA_AC_ID
)
VALUES (
@ServiceAppointID,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID
);

END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE() ,
@errortable = 'SERVICE_APPOINTMENT_CUSTOMER',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();

--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[LOG_SERVICE_APPOINTMENT_ERROR]
(
FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
AppointmentNumber ,
RONumber ,
CustomerName ,
CustomerHomePhone ,
CustomerEmailAddress ,
AppointmentDate ,
AppointmentTime ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleVIN ,
ServiceAdvisorNumber ,
OperationCode,
ComplaintStatement ,
Comments ,
CustomerFirstName ,
CustomerLastName ,
CustomerAddress ,
CustomerCity ,
CustomerState ,
CustomerZip ,
CustomerCellPhone ,
CustomerNumber ,
CustomerWorkPhone ,
Department ,
CASS_STD_LINE1 ,
CASS_STD_LINE2 ,
CASS_STD_CITY ,
CASS_STD_STATE ,
CASS_STD_ZIP ,
CASS_STD_ZIP4 ,
CASS_STD_DPBC ,
CASS_STD_CHKDGT ,
CASS_STD_CART ,
CASS_STD_LOT ,
CASS_STD_LOTORD ,
CASS_STD_URB ,
CASS_STD_FIPS ,
CASS_STD_EWS ,
CASS_STD_LACS ,
CASS_STD_ZIPMOV ,
CASS_STD_Z4LOM ,
CASS_STD_NDIAPT ,
CASS_STD_NDIRR ,
CASS_STD_LACSRT ,
CASS_STD_ERROR_CD ,
NCOA_AC_ID ,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable)
;
END CATCH

--================================================================================================================================================
-- **************************************************Insert into SERVICE_APPOINTMENT_DETAILS Table************************************************************
--================================================================================================================================================

BEGIN TRY

INSERT INTO SERVICE_APPOINTMENT_DETAILS
(
ServiceAppointID,
AppointmentTime,
ServiceAdvisorNumber,
OperationCode,
ComplaintStatement,
Comments,
Department

)
VALUES (
@ServiceAppointID,
@AppointmentTime,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@Department
);


END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE() ,
@errortable = 'SERVICE_APPOINTMENT_DETAILS',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();

--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[LOG_SERVICE_APPOINTMENT_ERROR]
(
FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
AppointmentNumber ,
RONumber ,
CustomerName ,
CustomerHomePhone ,
CustomerEmailAddress ,
AppointmentDate ,
AppointmentTime ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleVIN ,
ServiceAdvisorNumber ,
OperationCode,
ComplaintStatement ,
Comments ,
CustomerFirstName ,
CustomerLastName ,
CustomerAddress ,
CustomerCity ,
CustomerState ,
CustomerZip ,
CustomerCellPhone ,
CustomerNumber ,
CustomerWorkPhone ,
Department ,
CASS_STD_LINE1 ,
CASS_STD_LINE2 ,
CASS_STD_CITY ,
CASS_STD_STATE ,
CASS_STD_ZIP ,
CASS_STD_ZIP4 ,
CASS_STD_DPBC ,
CASS_STD_CHKDGT ,
CASS_STD_CART ,
CASS_STD_LOT ,
CASS_STD_LOTORD ,
CASS_STD_URB ,
CASS_STD_FIPS ,
CASS_STD_EWS ,
CASS_STD_LACS ,
CASS_STD_ZIPMOV ,
CASS_STD_Z4LOM ,
CASS_STD_NDIAPT ,
CASS_STD_NDIRR ,
CASS_STD_LACSRT ,
CASS_STD_ERROR_CD ,
NCOA_AC_ID ,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable)
;
END CATCH

--================================================================================================================================================
-- **************************************************Insert into SERVICE_APPOINTMENT_VEHICLE Table************************************************
--================================================================================================================================================

BEGIN TRY
INSERT INTO SERVICE_APPOINTMENT_VEHICLE
(
ServiceAppointID,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleVIN
)
VALUES (
@ServiceAppointID,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN
);

END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE() ,
@errortable = 'SERVICE_APPOINTMENT_VEHICLE',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();

--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[LOG_SERVICE_APPOINTMENT_ERROR]
(
FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
AppointmentNumber ,
RONumber ,
CustomerName ,
CustomerHomePhone ,
CustomerEmailAddress ,
AppointmentDate ,
AppointmentTime ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleVIN ,
ServiceAdvisorNumber ,
OperationCode,
ComplaintStatement ,
Comments ,
CustomerFirstName ,
CustomerLastName ,
CustomerAddress ,
CustomerCity ,
CustomerState ,
CustomerZip ,
CustomerCellPhone ,
CustomerNumber ,
CustomerWorkPhone ,
Department ,
CASS_STD_LINE1 ,
CASS_STD_LINE2 ,
CASS_STD_CITY ,
CASS_STD_STATE ,
CASS_STD_ZIP ,
CASS_STD_ZIP4 ,
CASS_STD_DPBC ,
CASS_STD_CHKDGT ,
CASS_STD_CART ,
CASS_STD_LOT ,
CASS_STD_LOTORD ,
CASS_STD_URB ,
CASS_STD_FIPS ,
CASS_STD_EWS ,
CASS_STD_LACS ,
CASS_STD_ZIPMOV ,
CASS_STD_Z4LOM ,
CASS_STD_NDIAPT ,
CASS_STD_NDIRR ,
CASS_STD_LACSRT ,
CASS_STD_ERROR_CD ,
NCOA_AC_ID ,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable)
;
END CATCH

---================================================================================================================================================
-- ***************************************************Move cursor to Next record ***************************************************************
--================================================================================================================================================

FETCH NEXT FROM SERVICE_APPOINTMENT_Cursor
INTO @FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@createddate;

END
CLOSE SERVICE_APPOINTMENT_Cursor;
DEALLOCATE SERVICE_APPOINTMENT_Cursor;
GO

SET ANSI_PADDING OFF
GO

-- ===============================================================================================================================================
-- *************************************************** END OF FLATFILE_SALES TABLE ***************************************************************
--================================================================================================================================================

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-10-28 : 10:41:21
Put your script in a stored procedure and call the stored procedure from SSIS (Execute SQL task)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site