March 17, 2009 at 5:30 am
I have 2 instances, I want to:
1. Extract data from one table in Instance 1
2. Import the data into one table in Instance 2
What tools in SSIS would I use to do this?
Thanks
March 17, 2009 at 6:08 am
Use the import / export wizard as this will build the entire package for you. you will be then be able to view and edit this package to see what has been created.
It will be avery simple ssis package consisting of a data source and data destination connection
March 17, 2009 at 8:20 am
Thanks Steve.
I used the import / export wizard and it worked well, BUT
this is somthing I want to run everyday, i.e. its basied on some SQL
Select * from clients
where newclients >= 20090101
So the problem is, that the process cant be use to UPDATE/INSERT into the table....
Any ideas?
March 17, 2009 at 8:30 am
It is possible to set this up to run each day, further inserst will be added to your tabel.
For updates you will need to write an update T-SQL statement and then connect the data flows to the input parameters for the stored procedure
March 17, 2009 at 8:32 am
Now that you have used the wizard to create the basic package, you are in a position to edit and refine it ... You can definitely make it do exactly what you want, given a bit of knowledge - which you will get here, hopefully.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 17, 2009 at 10:05 am
OK thanks Guys.
My first import went fine.... It created a table called Query and inserted the data from my other DB.
Now, I want to run the SSIS again, so I have changed the CREATE to an INSERT.
I changed the SQL Statement as follows
INSERT INTO [CentralLogging].[dbo].[Query]
([job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration])
GO
This fails, error:
SSIS package "Package1.dtsx" starting
Error: 0xC002F210 at Preparation SQL Task, Execute SQL Task: Executing the
query "
INSERT INTO [CentralLogging].[dbo].[Query]
([job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration])
" failed with the following error: "Incorrect syntax near ')'.". Possible failure
reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task
SSIS package "Package1.dtsx" finished: Failure.
March 17, 2009 at 10:11 am
You shouldn't have to change the create to an insert.
If you want to insert further data into the table then delete the part of the package that deletes and creates the table, then run the package with this table as the destination and the data will be added
March 17, 2009 at 11:10 am
WooHoo thanks! I spent about 45minutes editing and messing around with and, and all I had to do was delete it...!! :w00t:
What I have done is import the data from the system DB MSDB table "sysjobservers"
into another DB/Table
I want to do is do this for all my DB, therefore I will end up with one central DB/Tbale with all the JOB history in.
For my INSERT I am using a
SQL Command within DataFlow --> OLE DB Source Editor to import from the source data.
Why is does the MSDB table "sysjobservers" save the "Last_Run_Date" as 'INT'?
How can I convert this during import?
Thanks
March 17, 2009 at 11:15 am
a data conversion transformation should be able to convert the date, you may need to convert to a string first if it does not work converting from an integer
March 18, 2009 at 8:18 am
I have done the following:
Source Data --> DataConversion on Date from Four-byte Single Int (DT_I4) --> DataConversion to Date (DT_Date)
This fails.....Errors:
tblBook, Data Conversion 1 [281]: Data conversion failed while converting column
"Copy of last_run_date" (277) to column "Copy of Copy of last_run_date" (295).
The conversion returned status value 2 and status text
"The value could not be converted because of a potential loss of data.".
tblBook, Data Conversion 1 [281]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "output column "Copy of Copy of last_run_date" (295)" failed because error code
0xC020907F occurred, and the error row disposition on "output column
"Copy of Copy of last_run_date" (295)" specifies failure on error. An error occurred on the specified
object of the specified component. There may be error messages posted before this with more information
about the failure.
tblBook, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component
"Data Conversion 1" (281) failed with error code 0xC0209029. The identified component returned an error
from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause
the Data Flow task to stop running. There may be error messages posted before this with more information
about the failure.
Any ideas?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply