March 7, 2020 at 1:27 pm
Hi,
Issue: I had created my first package using Project deployment model, deployed package into sql server 2017 catalog. Package have project parameters InitialCatalog and ServerName. Created environment variables in Integration services catalog, added it in the references in the project properties. while executing package referred to the environment variables, execution is successful and also could see parameters are correctly referred as per the reports which are automatically generated. But i see no values are inserting in a table. The package is very simple just inserting into a table, which is successful during package execution on solution explorer.
Please help, i have watched many videos and followed same as they suggested, couldn't figure out. I have been trying all ways and checked many sites.
Thanks
March 8, 2020 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 8, 2020 at 3:46 pm
Check out the All Executions report for your package and see whether that gives you any clues.
What is the source of the data? Is it a file? If so, this may be a permissions issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 8, 2020 at 4:50 pm
Hi,
This is very simple package, just inserting one row into a table. I had tested in solution explorer in ssdt which works fine. only issue with SSIS Catalog after deployment why it is not inserting any rows, package is successful from job point and also from individual execution from SSIS catalog..
Couldn't figure out issue, being looking into this issue from almost 10 hours not able crack down..
Any help will be much appriciated.
Thanks
March 8, 2020 at 6:05 pm
I did try to help, but it seems you have not read what I wrote. Please provide the info requested.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 9, 2020 at 5:22 am
Hi,
Source is OLEDB, inserting into a table with hardcoded values, this is my first pacakge in 2017. below is the script i had in Source.
I checked reports, all reports shows success.
DECLARE @int int = (select count(*) from status)
INSERT INTO Status
select
@int ,
10,
'Test',
1,
getdate(),
null,
null
select count(*) as cnt from status
Thanks
March 9, 2020 at 8:09 am
some people have difficulty understanding what they are being asked to do so lets see if a explanation with images help.
follow the instructions on this link to see if you can find out the error.
https://radacad.com/ssis-catalog-part-5-logging-and-execution-reports
what you have posted so far is of no use for anyone in terms of identifying your error - or even if you are inserting into the wrong server/database
March 9, 2020 at 9:34 am
I have checked executions and reports, all success, one failure that i tried another way, otherwise you can see it is success in below screens. just a script which i copied in the previous post is inserting into a table where table is existing in the db which i used in the environment variables.
Thanks
March 9, 2020 at 1:35 pm
Hi,
Source is OLEDB, inserting into a table with hardcoded values, this is my first pacakge in 2017. below is the script i had in Source.
I checked reports, all reports shows success.
DECLARE @int int = (select count(*) from status)
INSERT INTO Status
select
@int ,
10,
'Test',
1,
getdate(),
null,
null
select count(*) as cnt from status
Thanks
If the above query is the source for the data flow, it needs to be changed. A data flow source should contain only the data which is to be inserted. In your case:
SELECT @int
,10
,'Test'
,1
,GETDATE()
,NULL
,NULL;
This will still fail, because @int is no longer defined. Change @int to 100 (or whatever) to verify that the INSERT works.
I suspect that you do not quite understand how a data flow is supposed to work. It requires as a minimum a source, a destination and mappings between the two.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 9, 2020 at 3:50 pm
Hi,
I am afraid to disagree with your comments, when i run the package from solution explorer it is success and could see the row got inserted. Also this is just a test package for understanding the deployment process how it works in 2017, so i went with minimum efforts to know.
I gave the execution screen right, if it fails it should show at least. I suspect it is with connection string not able to establish with environment variables, but i don't understand why, if that is the case any failure message will help to debug, but no failure too. so why its mangling...
Thanks
March 9, 2020 at 4:04 pm
Hi,
I am afraid to disagree with your comments, when i run the package from solution explorer it is success and could see the row got inserted. Also this is just a test package for understanding the deployment process how it works in 2017, so i went with minimum efforts to know.
I gave the execution screen right, if it fails it should show at least. I suspect it is with connection string not able to establish with environment variables, but i don't understand why, if that is the case any failure message will help to debug, but no failure too. so why its mangling...
Thanks
The only way that what you have written would be successful, is for it to run as an ExecuteSQL task. In which case, the concept of 'source' does not apply: instead, there is a Connection.
You can tell whether env variables are being assigned correctly by checking the All Executions report.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 9, 2020 at 4:47 pm
Screen from latest execution (All execution reports), which shows parameters values same as i had configured in environment variables. but it is not inserting any rows in that.. thats the confusion and frustration part here.
March 9, 2020 at 4:52 pm
Wow, that is one of the weirdest package designs I've ever seen! A source without a destination.
To make this work, please change things around to do things in the standard way, which in this case would be one of the following:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply