SSIS no longer writes data to file

  • AndrewSQLDBA

    SSC-Insane

    Points: 21827

    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

    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: 449

    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

    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

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply