Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INSERT variable values into a table

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.

like image 957
cometbill Avatar asked Jul 22 '10 10:07

cometbill


People also ask

How do you pass a variable in an insert statement in SQL?

This selects from on table and inserts into another... Then my insert statement to be something like this: INSERT INTO table2 (column1, column2, column3, ...) SELECT *@MY_NAME*, column2, column3, ...

How do you add a variable to a table in SSIS?

You can use an Execute SQL Task. Use the Parameter Mapping pane to map your servername to a variable. Then in the SQL Statement you can reference that variable and insert it into a table. The trick is you have to use a ? (question mark) as a placeholder for the variable (I have no idea what the origin of that is.)


2 Answers

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.

like image 143
cometbill Avatar answered Sep 27 '22 23:09

cometbill


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/

like image 45
KM. Avatar answered Sep 27 '22 23:09

KM.