Tag Info

New answers tagged

1

This post was exactly what I needed. I am adding to "billinkc"'s excellent code, with a little modification that I used to create command lines that will actually MOVE the packages to a different server: -- assumes default instance and localhost -- use serverproperty('servername') and serverproperty('instancename') -- if you need to really make this ...


0

I was able to accomplish this with a File System Task. Here's the info from MSDN: The File System task performs operations on files and directories in the file system. For example, by using the File System task, a package can create, move, or delete directories and files. You can also use the File System task to set attributes on files and directories. For ...


0

I had to use a Script Task at the Control Flow level instead of a Script Component at the Data Flow level. When I switched that around it worked like a charm. I also changed the sw.Write(fileText, System.Text.Encoding.ASCII); line to sw.WriteLine(fileText, System.Text.Encoding.ASCII); so it adds the text to the next line.


0

If you intend to run the SSIS jobs from the SQL Agent, then they will execute as the user the agent runs as. That's usually the same as the account the instance runs as, which has rights to everything.


1

There's no answer that applies in all cases. In general, however... If the lookup list is small and you can cache it(or use a cache data source), there's not much performance penalty to doing it in SSIS. If you want crossref a list of 50 location codes to names of cities, go for it. It's nice to see all the process on-screen in one place, rather than buried ...


0

I have done a bit of experimentation with non-relational datastores and found the integration tooling is usually poor or non-existant. It's probably more productive to approach this as a pull from the MarkLogic side than as a push from the SSIS side.


1

In that unpleasant case, I think the best strategy is to compartmentalize it away from SSIS. Either move that out to a linked server with an altering view or use some other etl tool like a powershell script to get it out of the dancing schema into a table with a fixed one. If you have any history you should be able to guess a reasonable value for the upper ...


1

That's a filesystem concern, not an SSIS one. Any file that SSIS creates will be owned by the user SSIS runs as. The best solution would be to set the ACL on the target folder to be what you want; when SSIS creates the file it will get those permissions by default. If you can't do that for some operational reason, you can manipulate the file after creation ...


0

In SSMS, go to SQL Server Agent Click on Jobs Now Click on View->Object Explorer Details You will have a list of all jobs on your right window. This way you can select multiple jobs that you want to script, right click and select "Script Job As" and then the options that you want.


0

I have a similar problem in SQL Server 2008. We have hourly schedule which pulls data from multiple Sybase Adaptive and Sybase IQ servers and populates the SQL Server (Using System DSN, Linked Server). Every Friday after 2 AM those jobs hang. After a little bit of research on my Windows Server which has the SQL Server I found that the McAfee weekly schedule ...


1

Since you're working with SQL Server 2008, you can take delete first approach as I outlined on this SO answer http://stackoverflow.com/a/15508174/181965 A option, that might sound stupid but it works, is to delete first and use the OUTPUT clause. I created a simple control flow that populates a table for me. IF EXISTS ( SELECT 1 FROM sys.tables AS T ...


4

SSIS gets its power by being an in-memory transformation engine. The base unit of work within a data flow task is the buffer. If you ever wonder why SSIS is so persnickety about data types, it's because it calculates the cost for a row and then allocates memory for N rows. All* the downstream components use the same memory address to do their part of the ...


0

I think it would be helpful to know why you are deleting the information from the source table as well as what kind of table it is(stage, etl, dim, etc..). It wouldn't help either if you specified whether you want to delete the table itself or just data in the table. Since you specifically said "also delete the data in the source table" I will take it that ...


0

Open the package in Data tools open data flow, open all async controls and click on ok and close the package. Re-build a solution and deploy


1

The issue here is that the Locale settings of the file connection in SSIS are different to the language settings in SQL Server. In SSIS, go to the Properties window for your File Connection (Right-click->Properties). There should be a setting for LocaleID that will need to be set to match SQL Server (or to English). Further information can be found in the ...


1

A derived column won't be able to add a row number. Well, it can add a column called RowNum but the expression language doesn't support the ability to update a value on the fly. Instead, you'll need a Script Component. You can search for "SSIS generate surrogate key" and find plenty of reference implementations. I'm going to borrow from ...


1

I experienced this problem, and was able to resolve it by disabling a DDL trigger on the SSISDB database for DDL_DATABASE_LEVEL_EVENTS. The trigger was trying to write info into another Database.



Top 50 recent answers are included