Select Statement Output Variable to Write to CSV

[jlee1]

Hi everyone,

I'm running a test that writes data to a database table. I successfully do so and then I want to run a select query so that I put the data into an output variable to be written to a csv file. When I get to the Select statement I receive this error: ERROR [8044 - Write Invoice Total to File] Last known record near row 0

There is 1 row and 1 column in the table and I just want to write it to a CSV file. From my google research it sounds like the data is being truncated. Does anyone have thoughts or suggestions? Thanks!

[Support_Rick]

Make sure the variable you are setting the value to after the SQL|Query is version 1

as in ...

SQL ResourceID
Query "Select * from Table ..." - OutputRowsetVar="MyRowValue"

<setVariable label="SetVar: MyValue" name="MyValue" value="${MyRowValue[1]" version="1.0" />

[jlee1]

Hi Rick,

Thanks for the input! I never actually declared a variable outside of the Select Task. Would you suggest I do that? I didn't see anywhere that I could put "Version 1.0" into the code and when I manually did it it gave me an error. Here is the code that I am using. This is my select task followed by my write to CSV task. Any thoughts?

Code: Select all

<sql label="Select Rows and Invoice Total" resourceId="DCP-NT9" version="1.0">
				<query label="total control" outputVariable="totalControl">
					<statement>select top 2 * FROM [master].[dbo].[fileSummary]</statement>
				</query>
			</sql>


			<writeCSV label="Write Invoice Total to File" inputRowSetVariable="${totalControl}" outputFile="unzipped/Control.csv" whenFileExists="overwrite" fieldDelimiter="comma" version="1.0" />

[jlee1]

OK so I adjusted my Write to CSV task so that it writes one portion of the rowset at a time, as in: ${totalControl[1] and ${totalControl[2]

However, this time I received error ERROR The result set has no current row.

So somehow it seems like my Select statement isn't pulling data and assigning it to the "out" variable called totalControl.

[Support_Rick]

jlee1,

There is an option while selecting tasks at the top of the page. Click on the check box and it will show you earlier versions of the task. That's where you'll find the Version 1 of the SetVariable task.

The code you have should work for multiple row selection and input into CSV files.

[jlee1]

Hi Rick,

I just can't seem to figure this one out. I keep geting this error when I write the database rowset variable to a csv: ERROR [8044 - Write Invoice Total to File] Last known record near row 0

So far I've written to the database, Select from the database(select statement works when I manually do it), put the Select output to a variable, and then I fail at the write to CSV. Any thoughts? Thanks again!

[Support_Rick]

JLee,

Based on our conversation, have you figured out what you need to do with this?

Please let us know...