SSIS no longer writes data to file

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  October 14, 2013 at 4:46 pm

  #1658374

  I always use stored procedures for all of my SSIS Execute SQL Tasks. This makes modifications and troubleshooting so much more simple. Only one place that things can go wrong after the package is built and deployed. And if something does need to be modified, it is only a sproc away, and all is done.

  Andrew SQLDBA

• kell 5514

  Newbie

  Points: 6

  More actions

  October 14, 2013 at 5:47 pm

  #1658377

  Thanks. I tend to use stored procedures but usually not for these things (SSIS packages). I did try it though; combined the two parts and made a stored proc. It failed: "A rowset based on the SQL command was not returned by the OLE DB provider." - which looks like the symptom of nothing being returned. Not sure why, but brute force tells me to keep the two parts separate. So I am moving on... till the next bizarreness.

• mikeg317X

  SSC-Addicted

  Points: 479

  More actions

  February 20, 2014 at 8:56 am

  #1690735

  I am getting this error now, almost to the T as the OP. But i figured out a work around.

  What was not communicated/grasped above, was that the data viewer does not even pop up at all, which i have never seen before.

  I even attempted the solution the OP posted for the heck of it, but it did not work, because my query was hand written moments ago in SSMS, and there were no bad chars hiding.

  extremely simple query, I am creating 6 rows in a single column.

  My query was using a table variable, which i believe to be the cause of this issue.

  example:

  DECLARE @premium TABLE ( col1 [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS )

  INSERT INTO @premium

  VALUES ('TL65550212131101 R ')

  ,('SC0765556&31010000000000000000000')

  ,('SC1065556&31010000000000000000000')

  ,('SC0165556&31010000000000000000000')

  ,('SC0565556&31010000000000000000000')

  ,('SC0365556&31010000000000000000000')

  SELECT * from @premium

  However, when i switched to a UNION ALL, the data viewer popped, and the flat file was created. strange issue, especially because the query preview works.

  fixed:

  SELECT ('TL65550212131101 R ') AS col1

  UNION ALL

  SELECT ( 'SC0765556&31010000000000000000000') AS col1

  UNION ALL

  SELECT ( 'SC0765556&31010000000000000000000') AS col1

  UNION ALL

  SELECT ( 'SC0765556&31010000000000000000000') AS col1

  UNION ALL

  SELECT ( 'SC0765556&31010000000000000000000') AS col1

  UNION ALL

  SELECT ( 'SC0765556&31010000000000000000000') AS col1

• riskaone

  Grasshopper

  Points: 17

  More actions

  April 15, 2019 at 4:11 pm

  #3380085

  If you have longer T-SQL script with several selects, the SSIS pickups the first select which produces "a resultset". Usually, the last select is used to produce the final result. To avoid this use:

  SET NOCOUNT ON

  at the begging of the script

• PankajWagh

  Newbie

  Points: 1

  More actions

  October 3, 2019 at 2:19 pm

  #3685724

  I ran into the same issue as mentioned today.

  I solved it by removing the "USE <DatabaseName>" from the sql query given in the Source component.