June 13, 2009 at 1:22 am
I'm new to SSIS from DTS and I'm wondering if I'm missing something.
I'm exporting tables from one server to text files and then importing them to another. So the table structures are identical.
To setup such an import in DTS, you create the two connections, map the columns and it's done.
(Let's ignore the Wizard for now:)
In SSIS:
1. I create a dataflow task
2. I create the connection to the flat file
3. I create the connection to the DB
4. I create the flat file source dataflow
5. I create the OLEDB destination dataflow
Now this is the part that really gets me: In step 2 - on the Advanced tab, I have to set the data type & width of each column. Is this really necessary?
The target table structure is the same as the source table structure, so now for each column in each table I have to set this definition up? If I get the definition wrong, the load will fail, which is no different to it failing because it can't insert the row into the same definition table!! So I've just got a whole lot more work to do for no benefit...
There's the suggest types button, but that doesn't work very well. For a table that was exported from DTS to a simple text file, it gets a whole lot of types & lengths wrong, so I dont see myself using that too ofter.
Am I missing something? This is the easiest import imaginable and it's just got a whole lot more complicated!!
Thanks
Sean
July 1, 2009 at 7:18 pm
You do need to do that step and define the structure, there isn't an easy way out.
Also, I have a question, what are you using files?
Can you not connect directly to the source and destination, I can almost guarantee the the copy will be fast, especially if you are trunc'ing the destination table, then you can use fast bulkload..
Just asking..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply