Mark a textbox "not for data-csv export"

Is it possible to mark a textbox in a report to not be exported to csv. This
would be similar to the "Data Output" tab for textboxes - but for csv instead
of xml?Setting the DataElementOutput value affects all "data-centric" rendering
extensions. I.e. CSV and XML. You cannot control CSV specifically in this
case.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:F6027B38-C0C4-4C89-B9B2-5CCD6A33987B@.microsoft.com...
> Is it possible to mark a textbox in a report to not be exported to csv.
> This
> would be similar to the "Data Output" tab for textboxes - but for csv
> instead
> of xml?sql

Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Mapping Columns Automatically?

New to SSIS...

I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?

Use the Export Data Wizard in SSMS.

-Jamie

|||Thats fine and dandy when starting from scratch. But if you have spent a lot of time building scripts and other actions in an existing package... it seems that it should be simple to add all columns to an existing text export. This seems like it would be such a common issue there has to be a solution.|||

You could replace your existing source adapter with a new one. The default behaviour is to select all columns which by the sound of it is what you want.

The new columns will automatically appear in the metadata of downstream components.

-Jamie

|||Thanks.. I will try that and see how it goes.

Mapping a string field to Boolean output in SELECT clause

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

Hi,

try this here:

SELECT Column1 AS Col1, Column2 AS Col2, CASE WHEN TypeColumn = "AAA" THEN 1 ELSE 0 END AS Col3
FROM myTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||It works!
Thank you, thank you, thank you!!!!!!!sql

Manipulating output

What can I do to accomplish the following:

Output 13 character values with leading zeros (599826 output as 0000000599826)

Outputting 30 char values with trailing blanks if necessary (20 char string with 10 char trailing blanks/spaces)

Can this be done in SQL?

Any help would be appreciated.Which DBMS?

For Oracle use LPAD and RPAD functions, e.g.

SQL> select lpad('599826',13,'0') from dual;

LPAD('599826'
----
0000000599826

SQL> select rpad('599826',13,'0') from dual;

RPAD('599826'
----
5998260000000|||I'm sorry, I'm using SQL Server 2000.|||I just took a look at the SQL Server docs and couldn't see equivalent functions - but then I'm no SQL Server expert. However, you could do it with a combination of other functions - something like:

left('0000000000000',13-len(string))||string|||What can I do to accomplish the following:

Output 13 character values with leading zeros (599826 output as 0000000599826)

Outputting 30 char values with trailing blanks if necessary (20 char string with 10 char trailing blanks/spaces)

Can this be done in SQL?

Any help would be appreciated.The leading zeros are relatively easy as long as you don't have to cope with negative numbers. To zero fill positive numbers on the left, you can use:SELECT Replace(Str(599826, 13), ' ', '0')Dealing with negative numbers is enough more complicated that I recommend a user-defined function to avoid the expression clutter.

To space fill a string on the right, you can use:SELECT Cast('xyzzy' AS Char(30))These can also be combined if you like.

-PatP|||PatP, thanks.