May 2, 2011 at 9:24 am
Hi,
I have a flat (width delimited) file that contains a lot of data that I don't need and a some that I do.
I figured that I would simply create an OLE DB connection and with script write a BULK INSERT statement to load the data in a temp table, parse it, and use the parts that I need.
Now based on previous experiences I know that SSIS doesn't agree with temp tables but works great with temp variables. Unfortunately the BULK INSERT statement doesn't agree with variables.
Here's an example of code that works perfectly well in SSMS but not SSIS.
DECLARE @temp TABLE
(
Part1VARCHAR(5),
Part2VARCHAR(5),
Part3VARCHAR(5),
Part4VARCHAR(5)
)
-- Create a temp table to bulk load the file
CREATE TABLE #tmp
(
filler CHAR(21)
)
BULK INSERT #tmp
FROM 'C:\tmp.txt'
WITH
(
DATAFILETYPE = 'char',
ROWTERMINATOR = ''
)
INSERT INTO @temp
SELECTSUBSTRING(filler,1,5) AS Part1,
SUBSTRING(filler,6,5) AS Part2,
SUBSTRING(filler,11,5) AS Part3,
SUBSTRING(filler,16,5) AS Part4
FROM #tmp
SELECT * FROM @temp
DROP TABLE #tmp
Does anyone know how to tackle this problem?
PS I don't want to create/drop a physical table because the server is replicated and it complains when new objects get created. It's fine with temp objects though.
Thanks,
Sam
May 2, 2011 at 9:29 am
Why do you need SSIS if you can do everything you need in T-SQL?
May 2, 2011 at 9:32 am
We are looking to use SSIS for scheduling and we also need to log the data retrieved on a different server.
With that in mind, we're using an OLE DB Source to connect to the production server where the BULK INSERT and changes are affected, then we will use an OLE DB Destination to connect to the BI server for logging.
May 2, 2011 at 9:38 am
smoo (5/2/2011)
We are looking to use SSIS for scheduling and we also need to log the data retrieved on a different server.With that in mind, we're using an OLE DB Source to connect to the production server where the BULK INSERT and changes are affected, then we will use an OLE DB Destination to connect to the BI server for logging.
Don't understand the scheduling bit - SQL Agent can schedule SSIS packages or T-SQL whenever you want.
Instead of using DROP/CREATE (for the reasons you mention) why not just create a permanent new physical table to act as a staging area and just run a TRUNCATE on it as the first step in your package before loading in your data?
May 2, 2011 at 11:10 am
Unfortunately the people in charge of our production applications frown upon keeping staging tables on the same database/server.
May 10, 2011 at 12:03 pm
I found a link which helps me solve the problem by using global temp tables:
It helped solve the problem.
May 16, 2011 at 10:46 am
smoo (5/2/2011)
Unfortunately the people in charge of our production applications frown upon keeping staging tables on the same database/server.
In those cases I might consider creating a persistent temp table in tempdb, basically a regularly named table that is specific to my process, I create it at the start and destroy it at the end. If it exists when I start I can drop and re-create it.
CEWII
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply