Tag Info

New answers tagged

3

Option A Take a command and control approach. Designate your newest SQL Server tier of boxes as the runner of all things SSIS. Your connection manager for SQL Server will be able to speak the appropriate TDS dialect for everything in your enterprise. Knowing nothing else about your organization, I would generally favor this approach. The management ...


1

You may run packages created with "any" earlier version of SSMS using dtexec, they seem to maintain backward compatibility. https://msdn.microsoft.com/en-us/library/bb522577(v=sql.110).aspx Check out the "Other versions" widget !


0

Community Wiki answer created from an answer in the question comments by billinkc The version of the DTS Wizard that is launched is or isn't licensed based on the installation. Here you can see that I am licensed for SSIS on my 2008 instance but not for the 2014 (because I only installed client tools): You can have a valid SSMS install and even a ...


2

Back in 2007 Andy Leonard posted an answer on the Microsoft forums that looks much like your problem in the following link. In 2013, Chris Johnson noted that the answer still worked for him. https://social.msdn.microsoft.com/forums/sqlserver/en-US/118d30fb-6748-49f4-94a8-509bc1066d61/execute-package-task-says-login-failed-for-sa Andy's post noted the ...


2

If you have OverwriteDestination set to False and your destination file already exists, then you will get an error stating something along the lines of [File System Task] Error: An error occurred with the following error message: "The file 'XXXXXXX' already exists.". That is the default behavior if you do not chnage anything else.


1

I had similar problem earlier and what I found is because the creator of the Package and executor of Package were different user it was having some issues. To get around this issue, I had to protect the package with password and I used that password to access the package in my Job. You should give that a try, I hope it helps. Also, is your package doing ...


1

You mention that you already have a piece of sql that does what you want. So i would suggest you modify that to become an update statement and implement it as a sql task. Another option is to save the update within a stored proc and call/execute it from ssis. With a bit of thought you could probably write an update statement that only sets the values for ...


0

apparently this is because I need to download and install Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 http://www.microsoft.com/en-us/download/confirmation.aspx?id=42313 I will get this done and if it all works I will accept this answer.


0

I have several easy to apply advises, drawn from my own personal experiences when "pulling" data from a transactional Oracle database into a "staging" database (under Microsoft SQL Server 2012 / 2014): In the SSIS packages do strive to use Oracle's own OLE DB Provider. Likewise, use Microsoft's latest OLE DB Provider for the ADO Destination. REDUCE (yes, ...


5

For SQL Server 2005 and 2008 R2, the only way to get the Integration Services development environment, Business Intelligence Design Studio a.k.a. BIDS, was through the installation media aka you already bought Developer, Standard or Enterprise Edition. However, if you're looking to purchase SQL Server for the first time, it'd be insane to spend that level of ...


1

Developer Edition was created for this specific reason and can be used perpetually. It was $50 USD last time I looked. Like the Eval, it has all the Enterprise Edition features. If that's not the target for deployment, take care not to use those features. Another thought is that Amazon offers AWS instances that include SQL Server licensing built in. You can ...


1

Apparently this just boils down to SSIS treating any varchar larger than 128 as NTEXT. Not sure why. I can, however, go into the advanced properties of the ODBC source and change the types back to something like DT_WSTR. Which seems to work for the most part. However, I did determine that a few of the tables I'm dealing with actually are carrying upwards of ...


2

SSIS does comes with SQL Server evaluation. Additionally if you're a small company or a start up you could take advantage of the Bizspark program and test the full version throughout and/or develop with it for 3 years. And to spice it up a bit this article can give you an additional input on the subject before you jump in: The Hidden Costs of SSIS


5

You can use Evaluation Edition for that, yes. Beware that it has the functionality of Enterprise Edition, and you plan to have Standard Edition in production, which has less functionality. But if you are careful about what you use, you'll be fine.


2

Since the link that I created in the comments did not contain this particular method, I figure I would throw it in here. Besides the approaches mentioned here I went ahead and tried this approach as another way that would take very little change to a package that is going through a ForEach Loop (since that is how I did it in mine). What you would do, is ...


2

Before SQL Server 2012 and the project deployment model, the only way to run an SSIS package from the context of SSMS was either a SQL Agent job or xp_cmdshell. SQL Agent is calling DTEXEC.exe as it has a special parameter /X86 that is only respected when passed from SQL Agent. Otherwise, you'll want to specify the explicit path to your dtexec.exe to ...


2

First (if you didn't already know) DTExec is the command line tool used to run an SSIS package. As far as I know even SQL Jobs use DTExec under the covers. As far as why they are using a bat file rather than the native GUI step I couldn't tell you. It could be that they were more comfortable with the bat file, or even didn't know that the option was there ...


0

As Kin mentioned in his comment above, it is possible to accomplish what I was trying to do using the SSIS Lookup transformation component (example also provided in his comment) which answers my question. While I will surely be using this component in the future I decided to go with a more straightforward SQL scripting method to accomplish what I needed ...


2

The only actions that will cause the values in your configuration table to be updated within SSIS will be when you are in the Configuration editor menu. Clicking OK there will cause the values to be written to the table. Now, if you have an Execute SQL Task that has UPDATE statement in it, that would obviously change the values in the table but those would ...


5

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently). If you are using traditional SQL then you probably don't need 1), but you might need 2). Your ETL must be running in ...


0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file? The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat ...


0

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...



Top 50 recent answers are included