June 23, 2015 at 9:11 am
Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General Tab:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;
Parameter Mapping:
Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10
Variables:
location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table
Sample Data:
SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8
STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.
I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data. I am thinking maybe a foreach loop but I don't know even how to get started. Any help is greatly appreciated.
June 23, 2015 at 9:21 am
Adding to my post above:
I know I could accomplish this by calling separate queries by system and then running the stored procedure for each system separately.
I wanted to see if it could be done dynamically using the variables/parameters or something else.
June 23, 2015 at 11:42 am
What do you mean by "group all system 3 records"?
Are all the rows from the source table going to the same destination table? If they are why do you need to pass the system_cd? Can't you just move them all at once?
Can we see the code or a representation of the code in the stored procedure?
I'm just trying to understand why you think you need to loop.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 12:11 pm
If the vendor would have put system in the load table, this wouldn't be an issue. Since they didn't I can pull the system code from the 1st two digits of the location code and populate it into a field in the load table I am not using (extension). However, the destination table does not have system code, it has system_id. The vendor has included in their stored procedure converting the system code to system_id.
I wasn't sure if I would have to group the data by system before sending it through to the stored procedure for the parameter or if the stored procedure could handle changing the parameter for each record based on the system field that's why I thought I might need to have it loop. I am an SSIS beginner.
Here is the stored procedure:
USE [Locate]
GO
/****** Object: StoredProcedure [dbo].[sp_ml_location_load] Script Date: 6/23/2015 2:09:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:RAC
-- Create date: 2010-03-05
-- Description:Used to load location data from external data source
-- =============================================
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @nextkey int, @system_id int, @location_type_id int, @return_status int,
@locations int
declare @location_cd nvarchar(10), @name nvarchar(40), @street nvarchar(50),
@city nvarchar(35), @state_province_code nchar(3), @postal_cd nvarchar(20),
@country_cd nchar(3), @phone nvarchar(20), @extension nvarchar(10),
@fax nvarchar(20), @location_hours nvarchar(60), @rank nvarchar(10),
@region nvarchar(20), @contact_name nvarchar(50), @email nvarchar(255)
SELECT @system_id = system_id from system where system_cd = @system_cd
IF @system_id IS NULL BEGIN
PRINT 'Invalid system_cd'
RETURN
END
SELECT @location_type_id = location_type_id from location_type
WHERE location_type_cd = @location_type_cd
IF @location_type_id IS NULL BEGIN
PRINT 'Invalid location_type_cd'
RETURN
END
select @locations = count(*) from location_load
IF @locations = 0 BEGIN
PRINT 'location_load table is empty'
RETURN
END
select@nextkey = last_key_value
fromst_key (holdlock)
wherekey_name = 'blind'
update st_key
set last_key_value = @nextkey + @locations
wherekey_name = 'blind'
declare location_cursor cursor for
SELECT location_cd, "name", street, city, state_province_code, postal_cd, country_cd,
phone, extension, fax, location_hours, rank, region, contact_name, email
FROM location_load
open location_cursor
fetch location_cursor into
@location_cd, @name, @street, @city, @state_province_code, @postal_cd, @country_cd,
@phone, @extension, @fax, @location_hours, @rank, @region, @contact_name, @email
select @return_status=@@error
if @return_status<>0
begin
PRINT 'Fetch location_cursor error '
goto location_end
end
while(@@fetch_status=0)
begin
INSERT INTO location
(location_id, location_type_id, location_cd, system_id, name, street, city,
state_province_code, postal_cd, country_cd, phone, extension, fax,
location_hours, rank, region, contact_name, email, lastupdated, lastupdatedby,active)
VALUES
(@nextkey,@location_type_id,@location_cd,@system_id,@name,@street,@city,
@state_province_code,@postal_cd,@country_cd,@phone,@extension,@fax,
@location_hours,@rank,@region,@contact_name,@email,getdate(),'admin','Y')
set @nextkey = @nextkey + 1
if @return_status<>0
begin
PRINT 'Error insert location'
goto location_end
end
fetch location_cursor into
@location_cd, @name, @street, @city, @state_province_code, @postal_cd, @country_cd,
@phone, @extension, @fax, @location_hours, @rank, @region, @contact_name, @email
select @return_status=@@error
if @return_status<>0http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
begin
PRINT 'Fetch location_cursor error '
goto location_end
end
end
location_end:
close location_cursor
deallocate location_cursor
END
June 23, 2015 at 12:37 pm
Wow, that's an interesting stored procedure.
So just to be sure I understand, is the location_load table is the load table you are referencing in your initial post?
Are there more than one system_cd in the location_load table? It looks like the procedure just takes all the rows in location_load and then adds them to location using each system_cd.
In this case I'd do something like this:
Execute SQL Task - Select DISTINCT sys from load_table - putting the result into a SSIS Object Variable
For Each Loop Task - For Each ADO Enumerator with the SSIS Object Variable selected as the ADO Object Source Variable. Placing the sys code for that row in another SSIS Variable in the variable mappings tab mapping to to index 0.
Inside the For Each Loop Task your Execute SQL Task calling your SP.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 12:45 pm
Yes, the location_load table is the load table I am referencing in my initial post. This is were the data is coming from.
Yes, there are more than one system_cd in the location_load table.
I will try what you have suggested here. Thanks for your help.
July 6, 2015 at 12:44 pm
I tried to approach this another way as follows:
1. Re-wrote stored procedure to hard code the location_type_cd (leaving one parameter to deal with – system_cd) and added an if condition to only update the records if they didn’t exist in the table (see revised stored procedure at the end of this post).
2. Create Data Flow
a. Created a source - OLE DB Connection Manager with SQL command:
select system from location_load
Columns = system
b. OLE DB Command –
Connection Manager: OLEDBConnection
Component Properties: EXEC FL_CUSTOM_sp_ml_location_load ?;
Column Mappings – Input column = system; Destination Column = @system_cd
The above runs great, it inserts my records to the table but the SSIS continues to run the OLE DB Command and it never ends. What can I do/set to make it stop?
Also I can run the stored procedure in SQL Server Management Studio and it runs once just like it is supposed to. I must have something set wrong in SSIS.
Note: When I ran this without the “if not exist” statement in the stored procedure, it keeps running and tries to insert duplicate records (that it already inserted into the table) resulting in an SSIS error.
Revised stored procedure:
USE [Locate]
GO
/****** Object: StoredProcedure [dbo].[FL_CUSTOM_sp_ml_location_load] Script Date: 7/6/2015 2:51:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:RAC
-- Create date: 2010-03-05
-- Description:Used to load location data from external data source
-- =============================================
ALTER PROCEDURE [dbo].[FL_CUSTOM_sp_ml_location_load]
(@system_cd nvarchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @nextkey int, @system_id int, @location_type_id int, @return_status int,
@locations int
declare @location_cd nvarchar(10), @name nvarchar(40), @street nvarchar(50),
@city nvarchar(35), @state_province_code nchar(3), @postal_cd nvarchar(20),
@country_cd nchar(3), @phone nvarchar(20), @extension nvarchar(10),
@fax nvarchar(20), @location_hours nvarchar(60), @rank nvarchar(10),
@region nvarchar(20), @contact_name nvarchar(50), @email nvarchar(255)
SELECT @system_id = system_id from system where system_cd = @system_cd
IF @system_id IS NULL BEGIN
PRINT 'Invalid system_cd'
RETURN
END
SELECT @location_type_id = location_type_id from location_type
WHERE location_type_cd = 'Store'
IF @location_type_id IS NULL BEGIN
PRINT 'Invalid location_type_cd'
RETURN
END
select @locations = count(*) from location_load
IF @locations = 0 BEGIN
PRINT 'location_load table is empty'
RETURN
END
select@nextkey = last_key_value
fromst_key (holdlock)
wherekey_name = 'blind'
update st_key
set last_key_value = @nextkey + @locations
wherekey_name = 'blind'
declare location_cursor cursor for
SELECT location_cd, "name", street, city, state_province_code, postal_cd, country_cd,
phone, extension, fax, location_hours, rank, region, contact_name, email
FROM location_load
open location_cursor
fetch location_cursor into
@location_cd, @name, @street, @city, @state_province_code, @postal_cd, @country_cd,
@phone, @extension, @fax, @location_hours, @rank, @region, @contact_name, @email
select @return_status=@@error
if @return_status<>0
begin
PRINT 'Fetch location_cursor error '
goto location_end
end
while(@@fetch_status=0)
If Not Exists (Select * from location WHERE location_cd = @location_cd)
begin
INSERT INTO location
(location_id, location_type_id, location_cd, system_id, name, address_line_3, city,
state_province_code, postal_cd, country_cd, phone, extension, fax,
location_hours, rank, region, address_line_1, address_line_2, lastupdated, lastupdatedby,active)
VALUES
(@nextkey,@location_type_id,@location_cd,@system_id,@name,@street,@city,
@state_province_code,@postal_cd,@country_cd,@phone,@extension,@fax,
@location_hours,@rank,@region,@contact_name,@email,getdate(),'admin','N')
set @nextkey = @nextkey + 1
if @return_status<>0
begin
PRINT 'Error insert location'
goto location_end
end
fetch location_cursor into
@location_cd, @name, @street, @city, @state_province_code, @postal_cd, @country_cd,
@phone, @extension, @fax, @location_hours, @rank, @region, @contact_name, @email
select @return_status=@@error
if @return_status<>0
begin
PRINT 'Fetch location_cursor error '
goto location_end
end
end
location_end:
close location_cursor
deallocate location_cursor
END
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply