Insert a single row and return its primary key

Good question, took me a few tries to figure it out. Declare an SSIS variable of type Int32 (unless you need sizing for a bigint or numeric). I chose tablePk as mine. Option 1 Execute SQL Task General tab ResultSet: None SQL INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT ? = SCOPE_IDENTITY() Parameter Mapping tab … Read more

SSIS how to set connection string dynamically from a config file

First add a variable to your SSIS package (Package Scope) – I used FileName, OleRootFilePath, OleProperties, OleProvider. The type for each variable is “string”. Then I create a Configuration file (Select each variable – value) – populate the values in the configuration file – Eg: for OleProperties – Microsoft.ACE.OLEDB.12.0; for OleProperties – Excel 8.0;HDR=, OleRootFilePath … Read more

Microsoft.ACE.OLEDB.12.0 is not registered

Summarized: INSTALL 32 bit version of Microsoft Access Database Engine 2010 Redistributable. Uninstall 64 bit version if previously installed. The Excel connection manager is trying to use the ACE OLE DB provider in order to access the Excel file when the version is above 2007 (xlsx). Although your box is 64-bit, you’re using SQL … Read more

Parameterized OLEDB source query

Simple. Choose SQL command as the Data Access Mode. Enter your query with a question mark as a parameter placeholder. Then click the Parameters button and map your variable to Parameter0 in the Set Query Parameters dialog: More information is available on MSDN.