May 18, 2012 at 2:10 am
Hi All,
Please help me with using SSIS package for downloading the SQL server database hosted on Azure. I am new to SSIS packages.
Basically - we have a database in SQL Azure and we want copy of that database on our local machine daily. I want to automate this process of having daily backup of SQL Azure database locally. One of the solution I came across is using SSIS package and scheduling it.
Here is what I did -
a) I have fairly large database with number of tables. So instead of creating SSIS package in BIDS enviornment - I first created blank database on my local machine. Then used "Import" feature to import all the tables from SQL Azure. In the last step of Import process - I had option of saving it as a SSIS package. I saved SSIS package on File System. So now I have SSIS package.
b) But I can not use that SSIS package as it is because it will give me error of "table xxx already exists" etc next time. So I need to modify that SSIS package to add one SQL task - which will drop existing database at the begining. So every day I will be creating new database on local machine which is fine.
c) To do this - I opened the saved SSIS package in BIDS and added new SQL task at the begining. But if I try to run the SSIS package now - it gives me error at first Data Flow task. Please note that I havent made any changes to SSIS package (which was created with successfully Import) - except adding new SQL task at the begining. Even if I dont add that new task and simply run the SSIS pacakge as it is - it gives error.
I am not sure why SSIS package should give me error in first data flow task because this package was generated from successful import on local machine DB. The error message I am getting in log is :
OnError,RMG-FSYSHS1,RICH\Clarion,Data Flow Task 1,{35F63A4D-949A-4A84-860E-1DCC8854ED38},{1800E3FE-B6D4-4BD9-BA55-2849DC6B5228},5/18/2012 1:38:35 AM,5/18/2012 1:38:35 AM,-1073450910,0x,
Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
Does that mean -
a) I can not use the SSIS package saved from import as it is
b) OR I am missing anything
Please help me resolving this issue. I am using SQL 2008 R2 on Windows 7 64bit machine.
May 18, 2012 at 5:38 am
Are you trying to run the SSIS package in the same place (same machine, same account) the second time where you get the connection error as when you ran it originally?
I'm wondering if you ran it on the server via the wizard the first time (where it had permissions) and now you're trying to the SSIS package on your local machine with a different account?
Just a thought.
HTH,
Rob
May 18, 2012 at 7:57 am
Rob - I am running it on same development machine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply