SSIS

2-sql-servers3 most often reason, when you are using variables in Execute SQL task inside SSIS and get following error:

Error: 0xC002F210 ... , Execute SQL Task: Executing the query "...

..." failed with the following error: "HERE IS IMPORTANT PART". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

2-sql-servers.png

Sometimes you need to use a variable inside SQL in SSIS Data Flow Tasks. For example, you may want to select TOP x records in a periodically scheduled task. This X variable could be used to adjust performance or sometimes a condition cannot be specified when the package is created. And of course there are many other reasons for why you would want to use a variable in Data Flow Tasks. The problem is that, although you can put "?" in your SQL query for the DB source, you cannot set up the parameters there by clicking a button. And you will get an error message if you try to use this query. But there are ways to accomplish the same thing. In this article I will show you how to do it using Script Task.

SQL_icon_base.jpg I get strange error during redeveloping one SSIS. When I tried run some SQL task, finally such an easy one with "SELECT 1", I always received same error.

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following error: 80040154.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 

 

2-sql-servers.png

For some solutions in SSIS you may need to go over all records from some SELECT and each record use in some subqueries. In this case you want loop record per record in some task series. Best solution is to use Looping through recordset. Because of SSIS is intuitive enough to create this task, here is step by step overview. In this step by step tutorial, I am using AdventureWorks database from Microsoft. This database was created for testing purpose and is ideal for our example.