November 15, 2010 at 5:14 pm
Something so incredibly simple seems to be unbelievably complicated.
In 2005 Management Studio, I have gone through the Import/Export wizard to copy some tables from another SQL Server to this SQL server.
I have saved it as a SSIS package in SQL Server. So far simple.
1st problem. How do I open the package to edit it in BIDS? I have been through every single menu item in Visual Studio/BIDS & cannot find where to open the package I've saved.
2nd Problem. To work around the first problem, I created a new package & tried to add existing package. So I find the saved package & add it, but nothing happens - nothing seems to get added. I really just want to open the saved package as per first problem, not create a new one and add.
3rd Problem. I just want to schedule the package to run each night, so I have created a SQL Server Agent job, found the package, scheduled it. It doesn't run. "Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-11-15 23:00:01.80 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-11-15 23:00:02.17 Code: 0xC0016016 Source: Description: Failed to... The package execution fa... The step failed."
What would have taken me about half an hour in good old SQL 2000, has now wasted about 2 days and I am now going bald. I'm defeated. Can someone help?
November 15, 2010 at 5:34 pm
Danster (11/15/2010)
Something so incredibly simple seems to be unbelievably complicated.What would have taken me about half an hour in good old SQL 2000, has now wasted about 2 days and I am now going bald. I'm defeated. Can someone help?
Whelp, let's see what we can do for ya there, hoss. Yep, SSIS can be a real hoot to bend yer noggin' round, so doncha sweat it, y'hear? /End Bad Cowboy imitation.
In 2005 Management Studio, I have gone through the Import/Export wizard to copy some tables from another SQL Server to this SQL server.
I have saved it as a SSIS package in SQL Server. So far simple.
In theory. You really wanted to save this as a file. I'll explain why shortly.
1st problem. How do I open the package to edit it in BIDS? I have been through every single menu item in Visual Studio/BIDS & cannot find where to open the package I've saved.
Note your comment above, you saved it in SQL Server. Open up SSMS and in registered servers choose the last icon on the right, Integration services. Now, rt-click here and register the *exact same server name*. SSIS acts like an independent server.
2nd Problem. To work around the first problem, I created a new package & tried to add existing package. So I find the saved package & add it, but nothing happens - nothing seems to get added. I really just want to open the saved package as per first problem, not create a new one and add.
When you add existing package, you add it to the solution, not to the active package. On the right you should see the 'Solution Explorer'. If you don't, it's under the view menu. You should see an added package there.
Now, the reason you wanted to do this as a file is because it's much easier to manipulate the file to your final status, and then import it up to SQL Server. The password problem you describe sounds like it's because of the encryption method. My guess is you used a SQL Login, and password, in the package. SSIS considers that "Sensitive Information", and goes through great lengths to make it annoying as hell to deal with it until you're used to it. You will find your options for dealing with this under properties for the package. You want the security subheading, ProtectionLevel option.
3rd Problem. I just want to schedule the package to run each night, so I have created a SQL Server Agent job, found the package, scheduled it.
You are better off creating a jobstep, telling it to run SSIS package, and then finding the usable name that way in the SSIS server. Much less painful, and many more options to work from when you want them.
Hopefully that helps a bit, post back if you're still gettin' stuck. I'll check back in a while when I get home from work.
*rides off* Hiyeaaahhh! *waves hat* git along, ya little data... hyeah, hyeah!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply