sql serverssis
I'm new…
I've built an ETL process in SSIS that takes 46 flat files and loads them into stg.Suppliers and triage.Suppliers. Then another solution runs that applies Business Rules. And then the final solution moves the good stuff from stg.Suppliers into pdw.Suppliers.
My question is when I run all this (once a week); is it good (best practice) to clean up the buffer for the stg.Suppliers table? I notice that when I run this query that it's the number one cached_pages_count.
If yes, how?
You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.
Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.
With only that change, the package will execute successfully
Also, an excellent answer from SO on the same issue
If you've truncated the table, then a primary key violation must be coming from duplicate data in the file. Try bulk inserting into a new table, without the PK constraint, and then check the table for duplicates (probably easier than writing some tool or script to parse the file directly). You can create a mimic table that won't have constraints this way:
SELECT * INTO dbo.new_bulk_source
FROM dbo.old_source
WHERE 1 = 0;
Then change your package to reference this table, do the insert, then run:
SELECT key FROM dbo.new_bulk_source
GROUP BY key
HAVING COUNT(*) > 1;
I bet a donut the call is coming from inside the house (or the truncate is not succeeding).
Best Answer
Leaving it in cache will have no downsides since SQL Server does a great job managing the cache without interference. In fact, it may be beneficial to keep some of those pages cached.
Are you attempting to solve some problem?