ssis - INSERT variable values into a table

Question

I have several variables in an SSIS package that I would like inserting into a table.

example:-

@financialMonth, @Status, @Comments

The Variables have been populated along the way with values based on lookups, filename, dates, etc, and I want to store them in a results table.

Is using the execute SQL task the way to do this ?

Do I need to call a sproc and pass those variales as parameters ?

I've tried putting the following T-SQL into the SQLStatement property

INSERT INTO FilesProcessed 
   (ProcessedOn, ProviderCode, FinancialMonth, 
   FileName, Status, Comments) 
SELECT     GETDATE(), 'ABC' , 201006, 
   'ABC_201005_Testology.csv', 
   'Imported','Success' 

I tried hardcoding the values above to get it to work

These are the columns on the table I'm inserting into

Column_name     Type         Computed  Length
fileID          int          no          4
ProcessedOn     datetime     no          8
ProviderCode    nchar        no          6
FinancialMonth  int          no          4
FileName        nvarchar     no        510
Status          nvarchar     no         40
Comments        nvarchar     no        510

This is the Expression code that feeds the SQLStatementSource property

"INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, 
  FileName, Status, Comments) SELECT     GETDATE() AS ProcessedOn, '"
  + @[User::providerCode] + "' , " 
  + (DT_STR,6,1252)@[User::financialMonth] + ", '" 
  + @[User::fileName] + "', 'Imported' AS Status,'Successfully' AS Comments "

Unfortunately I'm missing something, and can't quite get it to work.

The Error message I'm getting is ... Error: 0xC002F210 at Log entry in FilesProcessed, Execute SQL Task: Executing the query "INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, FileName, Status, Comments) SELECT
GETDATE(), 'ABC' , 201006, 'DAG_201005_Testology.csv', 'Imported','Successfully'" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I2)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please

a). Advise whether the Execute SQL Task is the way to do what I want to do.

b). Give me any pointers or pitfalls to look out for and check.

Thanks in advance.

Answer 1

make sure that the data types of the VALUES match the destination column data types.

see: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e8f82288-b980-40a7-83a6-914e217f247d/

Answer 2

A couple of speculative suggestions

1. The Error message says An error occurred while extracting the result into a variable of type (DBTYPE_I2). But this is a straight insert statement. There shouldn't be a result except for rows affected. Do you have any parameter mappings erroneously set to Output?

2. What if you try and run the SQL Query from the error message directly in management studio? Does that give you an error?

Answer 3

Hi

In the above table definition FinancialMonth as int datatype as

FinancialMonth int no 4

while inseting casting as

(DT_STR,6,1252)@[User::financialMonth]

I feel it's purely a datatype mismatch with the target table definition.

thanks

prav

Answer 4

OK, here is what I did.

I created an Execute SQL task and configured, thus :-

General Tab
  ConnectionType = OLE DB
  SQLSourceType = Direct Input
  SQLStatement = (left blank)
  BypassPrepare = True
  ResultSet = None

Parameter Mapping
  (none - leave blank)

Result Set
  (none - leave blank)

Expressions
  SQLStatementSource = "INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, FileName, Status, Comments) SELECT GETDATE(), '" + @[User::providerCode] + "' , " + (DT_STR,6,1252)@[User::financialMonth] + ", '" + @[User::fileName] + "', 'Import - Success', '" + @[User::fileComments] + "'"

Then as long as I set up the variables and populate them in the variables window (the Expression editor will not let you save an expression that references a variable that does not exist. Keep notepad handy to store the contents while you go back and edit the variables window, and add new variables in ;)

Build the expression slowly, using the Parse expression button regularly to check.