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.

Solution 1

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.

Solution 2

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/

Comments

[Martin Smith]

That looks like it should work to me from what I remember about SSIS. (though obviously the code you posted isn't using the variables at all) What error are you getting?

[KM.]

try removing "as status" and "as comments"

[cometbill]

I've pasted the Expression code that I used to create the T-SQL. I was trying to hard code the T-SQL to see if the simpler code worked.