January 3, 2017 at 3:47 pm
Hi,
I am having trouble with SSIS package which should insert rows from my Object named EmployeeList.
I am not getting an error, but when it inserts into my table it only populates the EmpID column and
not the other 3 columns. It is looping correctly and I am getting all rows(only EmpID column) a total
of 287, but no second, third, or fourth columns, they are blank. I put a breakpoint on the foreach loop and
I am seeing the user variables being populated with the data from EmployeeList. So, has to be the
mapping on the last sql task(one inside the container). Below is my setup of my packages.
I must be missing something. Any help would be very much appreciated.
Thanks,
rtp
1. sql task which populates my variable object called EmployeeList
2. foreach container which will loop through the EmployeeList.
3. Set up 4 user variables
1 EmpID int
2 FirstName string
3 LastName string
4 JobTitle string
4. Set the above 4 variables to the variables mapping on the foreach container
5. Another sql task inside the foreach container, set as follows
Result is = none
setup my connections
SQL Statement = Exec EmployeesGreater15YearsServed ?, ?, ?, ? (Runs my stored Procedure)
Parameter Mapping is: (here I also tried with the index number(0,1,2,3) instead of SP name variables)
EmpID Input long @BusinessEntityID -1
FirstName Input nvarchar @FirstName 50
LastName Input nvarchar @LastName 50
JobTitle Input Nvarchar @JobTitle 50
6. Here is my stored procedure
Create Proc EmployeesGreater15YearsServed
@BusinessEntityID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50)
AS
Begin
Insert into EmployeesService15
(BusinessEntityID,FirstName,LastName,JobTitle)
Values (@BusinessEntityID,@FirstName,@LastName,@JobTitle)
End
7. Table EmployeesService15 is design exactly like the Proc variables w/o the @ sign.
January 3, 2017 at 6:11 pm
rtp (1/3/2017)
Hi,I am having trouble with SSIS package which should insert rows from my Object named EmployeeList.
I am not getting an error, but when it inserts into my table it only populates the EmpID column and
not the other 3 columns. It is looping correctly and I am getting all rows(only EmpID column) a total
of 287, but no second, third, or fourth columns, they are blank. I put a breakpoint on the foreach loop and
I am seeing the user variables being populated with the data from EmployeeList. So, has to be the
mapping on the last sql task(one inside the container). Below is my setup of my packages.
I must be missing something. Any help would be very much appreciated.
Thanks,
rtp
1. sql task which populates my variable object called EmployeeList
2. foreach container which will loop through the EmployeeList.
3. Set up 4 user variables
1 EmpID int
2 FirstName string
3 LastName string
4 JobTitle string
4. Set the above 4 variables to the variables mapping on the foreach container
5. Another sql task inside the foreach container, set as follows
Result is = none
setup my connections
SQL Statement = Exec EmployeesGreater15YearsServed ?, ?, ?, ? (Runs my stored Procedure)
Parameter Mapping is: (here I also tried with the index number(0,1,2,3) instead of SP name variables)
EmpID Input long @BusinessEntityID -1
FirstName Input nvarchar @FirstName 50
LastName Input nvarchar @LastName 50
JobTitle Input Nvarchar @JobTitle 50
6. Here is my stored procedure
Create Proc EmployeesGreater15YearsServed
@BusinessEntityID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50)
AS
Begin
Insert into EmployeesService15
(BusinessEntityID,FirstName,LastName,JobTitle)
Values (@BusinessEntityID,@FirstName,@LastName,@JobTitle)
End
7. Table EmployeesService15 is design exactly like the Proc variables w/o the @ sign.
May I ask why you did not create a data flow directly from the table referenced in (1) to EmployeesService15? Why the need for all the intermediate steps?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 3, 2017 at 7:32 pm
Hi,
Use a dataflow.
In your Source write a query to select all employees with > 15 year employment.
Map that to fields in your destination.
No muss no fuss.
I tend to save SPs for data transforms.
Cheers
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply