SSIS - Converting multiple tables to CSV

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS - Converting multiple tables to CSV

Author

Topic

dcuffee
Starting Member

3 Posts

Posted - 2010-06-10 : 11:22:33

So far no one has been able to help me on this issue. So I am hoping someone can help me solve this challenge.

What I am trying to do is first query a table with the following query:

SELECT 'SELECT * FROM ' + [ApplianceTypeTableName] FROM [AppSystem].[ApplianceTypes]

The ApplianceTypes table contains table names that I want to create a CSV file for. There are 68 tablenames in the table.

So what I have already done is create an SQL Execute task with the following properties:

User::TableNames is Type String.

General Tab: Result Set: Full Result Set, Connection Type: OLE DB, SQL SourceType: Direct Input, SQL Statement: Set to above SQL statement

Parameter Mapping Tab: No parameters

ResultSet Tab: Result Name = 0, Variable Name = User::TableNames

Expressions Tab: No expressions

I then have the arrow pointing from the SQL Execution Task into a Data Flow Task

Inside the Data Flow Task:

I have a OLE DB Source with the following properties:

Data Access Mode = SQL Command from variable

Variable Name = User::TableNames

I then have an arrow going from the OLEDB Source into a Flat File Destination.

The Flat File Destination has a Flat File connection with the Connection Property set to @[User::CSV_OutputDir] + @[User::TableNames] + ".CSV"

I know I probably have this whole thing setup wrong for what I am trying to do. This is my very first SSIS package so I know it's wrong.

Right now the package is red at the very first process with an error:

[Execute SQL Task] Error: Executing the query "SELECT 'SELECT * FROM ' + [ApplianceTypeTableName] FROM [AppSystem].[ApplianceTypes]" failed with the following error: "The type of the value being assigned to variable "User::TableNames" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Error: The type of the value being assigned to variable "User::TableNames" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Any help in resolving the error and telling me if I am approaching it correctly over all as far as the tasks I have established.

Thank you

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2010-06-23 : 22:40:25

first :
ResultSet Tab: Result Name = 0, Variable Name = User::TableNames
--> User::TableNames should be object type or DBNull type
second :
i dont understand that why you must use full result set to point arrow to data flow task.
may be you can try this :
1. use loop container
2. inside loop container is 3 task :
2.1 use scripts task to assign SELECT * FROM ' + [ApplianceTypeTableName] and Flat File Destination has a Flat File connection
2.2 use data flow to export data to csv file
2.3 use scripts task to set condition of loop container

gaauspawcscwcj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources