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
Here is one possible way of loading this file into SQL Server. Below shown example reads the contents of EDI 823 Lockbox file and loads into multiple tables along with the relationship. I am sure that there are other better ways of doing this. This is just one example of loading an EDI file into … Read more
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
Summarized: INSTALL 32 bit version of Microsoft Access Database Engine 2010 Redistributable. Uninstall 64 bit version if previously installed. http://www.microsoft.com/en-us/download/details.aspx?id=13255 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
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.
Looks like you have the right idea in general and the link @Speedbirt186 provided has some good details but it sounds like there are a couple of nuances that I thought I might point out in regards to flow and variables. The foreach loop can assign the entire path or the file name or file … Read more
Solution: Set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task. Here is a sample SSIS package written in SSIS 2008 R2 that illustrates using temporary tables. Walkthrough: Create a stored procedure that will create a temporary table named … Read more
The code from @garry Vass, or one like it, is going to be needed even if you’re using SSIS as your import tool. Within SSIS, you will need to update the connection string to your flat file connection manager to point to the new file. Ergo, you need to determine what is the most recent … Read more