CODEWITHSUNDEEP
ssis
Kung Fu Ninja
Kung Fu Ninja

Reputation: 3752

SSIS insert from object

I am working on an SSIS package with very limited knowledge. I have one "Execute SQL Task" that retrieves about 300 rows from a view. I put the result set into an employeeList object.

My next step is to insert these records to a table. I tried creating a ForEach Loop Container and put my "Execute SQL Task" into this container. My parameter mapping is :

variable name      | direction | datatype| parameter name | parameter size 

User::employeeList | input     | varchar | 0  | -1.
User::employeeList | input     | varchar | 1  | -1.
User::employeeList | input     | varchar | 2  | -1.
User::employeeList | input     | varchar | 1  | -1.

...

The error I get is : Error: 0xC002F210 at Populate Data, Execute SQL Task: Executing the query "INSERT INTO [dbo].[xxxx] ( [field1], [Perso..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_STR)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Upvotes: 0

Views: 2029

Answers (1)

billinkc
billinkc

Reputation: 61211

The Execute SQL Task is fine and dandy but for your situation, it sounds like a Data Flow will suffice.

Delete your existing stuff and drag a Data Flow onto your Control Flow. Double click the Data Flow and add an OLE DB Source and an OLE DB Destination.

In your OLE DB Source, change the OLE DB connection manager to the name of your source connection. Change the "Data access mode" from Table or View to SQL Command. The former is easier to click but incurs a slight performance overhead versus writing the equivalent SELECT * FROM mytable. In the no-longer-greyed-out window, put your query. 

SELECT column1, column2, ... FROM dbo.MyView

Generally speaking, you should enumerate all the columns you want. Pulling back columns you don't need wastes memory and that is what makes SSIS fast.

Wire the green arrow from the source to the "OLE DB Destination". Double click it and change the OLE DB connection manager to be your target database connection. In there, change the Data access mode to Table or view - fast load. This allows us to load data in bulk whereas the default Table or view issues an insert for each row that flows through and that's slow. Find the target table name, dbo.xxxx in the drop down and then click the Mappings tab. It should auto-map based on name matching. Go through and verify things map as expected. Click OK

Save and it should work.

Upvotes: 3

Related Questions