-1

I use my own login. I had to add a new join to OLEDB source query and tested with execute of Dataflow task and works 400k rows. Then run within package return is 0 rows and no error or warning

MacAries
  • 1
  • 3
  • Probably need to do this: https://learn.microsoft.com/en-us/sql/ssms/agent/configure-a-user-to-create-and-manage-sql-server-agent-jobs – Jacob H Apr 13 '18 at 20:12
  • yes I deploy to a server that uses an agent but because I use my login in test and get 2 different results I don't see a login or permission issue there may be something else regarding the join – MacAries Apr 13 '18 at 20:31
  • it works independently under table and fast load but when it runs as part of the package that fills other tables in the group it just skips right over returning 0 rows like it doesn't even try- no warning in the log either – MacAries Apr 13 '18 at 20:33
  • I take the join out and it runs with the package – MacAries Apr 13 '18 at 20:36
  • Can you post the query? – Jacob H Apr 13 '18 at 20:38
  • select bunch of columns from RMA_Stage INNER JOIN RMA_Status ON RMA_Stage.RMA_Status_Key = RMA_Status.RMA_Status_Key INNER JOIN RMA_Type ON RMA_Stage.RMA_Type_Key = RMA_Type.RMA_Type_Key --INNER JOIN Support_RMA_SuperType st --ON RMA_Type.RMA_Type_Natural_Key = st.SVC_Return_Type LEFT OUTER JOIN Product ON Product.Part_Number = RMA_Stage.Part_Number_Orig – MacAries Apr 13 '18 at 20:58
  • What does "run within package" mean? are you saying you deployed the package and ran it? from the catalog or from a SQL Agent job? – Nick.Mc Apr 14 '18 at 01:21
  • @Nick SSIS package has many Data Flow Tasks and Exec SQL Tasks. I successfully tested my joined query in SQL 2008, 2014, 2016. But does not work in SSIS 2014 in Test or deployed to 2016 Catalog or run by agent Can I have a thumbs up ? fixing this by removing the join worked for me – MacAries Apr 23 '18 at 13:33
  • Surely removing a join makes it do something different to what it's mean to do – Nick.Mc Apr 23 '18 at 22:35
  • Yes the join in my case set a column value and removing the join meant I had to add the column later with an update executable after the data load this is a good work around again can I have a thumbs up? – MacAries Sep 11 '18 at 18:12

1 Answers1

-1

SSIS gets confused with too many joins. If the joins are just replacing values with lookups then move one or more to a sql task and use an update statement after the load

MacAries
  • 1
  • 3