How to Insert Transaction Log to Database-CodePudding

I want to enter the table name, start time, finish time and number of rows into TABLE_LOG when I run the SSIS Package. So this is my Data Flow Task.

And this is my Execute SQL Task script for Start Time and Finish Time :

INSERT INTO TABLE_LOG (TABLE_NAME, START_TIME) 
VALUES ("EMPLOYEE_DESTINATION", GETDATE());

INSERT INTO TABLE_LOG (FINISH_TIME, NUM_OF_ROWS)
VALUES (GETDATE(), 6);

But the result I got was not what I expected. This is the result :

How can I do it? I also want to auto insert table name, not hard write like that. I am a beginner is SSIS. Sorry for my English

** Note (correction) : TABLE_NAME = DATABASE_NAME

CodePudding user response：

Why do I have two rows instead of one?

While it might not be what you expected, the Execute SQL Task is performing what you asked it to do. You have issued 2 INSERT statements and so you have the start record in the first row and the finish record in the second row.

If you want to change the data on an existing row in a database, you need to UPDATE it. That would make your Finish Time query look something like

UPDATE TL
SET
    FINISH_TIME = GETDATE()
,   NUM_OF_ROWS = 6
FROM
    TABLE_LOG AS TL
WHERE 
    TL.TABLE_NAME = 'Employee_Destination' 
    AND TL.FINISH_TIME IS NULL;

Now, the downside to your current table design is that if this package runs and fails, runs again and fails and runs a third time and succeeds, there will be 3 rows all with the same end date and number of rows. You want to have something unique on that row, like an autogenerated id, that you send back to the package when you do the insert. That will simplify your update statement because you just update where LogID = 1;

How do I make my entries dynamic?

You need to create SSIS Variables.

Create a variable for the TABLE_NAME of type String/DT_WSTR. Populate this with the name of the table you're going to send data to - Employee_Destination In the data flow, change the OLE DB Destination component from using Table Name - Fast Load to Table Name from Variable - Fast Load (text approximate) and in the Variable, use your new @[User::TABLE_NAME]

Create one for NUM_OF_ROWS of type Integer/Int32. Add a Row Count transformation in your data Flow between your Source and Destination. Choose this @[User::NUM_OF_ROWS]

And since you'll add an identify to your logging table, add a LogId of type Integer/Int32

You'll populate the LogId by making your insert into

INSERT INTO TABLE_LOG (TABLE_NAME, START_TIME) 
VALUES ('EMPLOYEE_DESTINATION', GETDATE());
SELECT SCOPE_IDENTITY();

Change your Execute SQL Task, Start Time, to have a single row result set output. In the Results tab, add an entry and for element 0, associate it to @[User::LogId]

However, you'll also need to look into parameterizing your query. Assuming OLE DB Connection Manager, that will take the shape of

INSERT INTO TABLE_LOG (TABLE_NAME, START_TIME) 
VALUES (?, GETDATE());
SELECT SCOPE_IDENTITY();

On the input parameters, variable name 0 is @[User::TABLE_NAME]

Finally, your update will also take parameterization

UPDATE TL
SET
    FINISH_TIME = GETDATE()
,   NUM_OF_ROWS = ?
FROM
    TABLE_LOG AS TL
WHERE 
    TL.LogID = ?;

Element 0 is @[User::NUM_OF_ROWS] and element 1 is @[User::LogId]