SQL 2012 :: Running 2005 SSIS Packages That Read FoxPro Files?

Aug 26, 2013

We are in the process of upgrading to a new SQL 2012 server but we have many packages that load data from dbf files created with FoxPro into one of our databases. We have not converted the packages and run them with DTS but we get the following error:

Error: 2013-08-26 11:05:27.36
Code: 0xC0209303
Source: BenchmarkLoad Connection manager "OLEDB NPIONE.Investment.middleTierSQL"
Description: The requested OLE DB provider SQLNCLI.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

[Code] .....

I searched for OLEDB and ODBC drivers for SQL 2012 64 bit but cannot find any that is newer than the 2005 that we have. What can I do short of changing the source files to overcome this issue?

View 8 Replies


ADVERTISEMENT

SQL 2012 :: Upgrading SSIS Packages With Miscellaneous Files

Mar 2, 2015

I have an SSIS package that was originally written for SQL Server 2008 that needs to be updated. I am interested in moving it to 2012 and using the project deployment model, but it includes a plain text script file in an Execute Process Task call to WinSCP for an SFTP transfer. It appears that by moving from package deployment to project deployment that this file would no longer be bundled with the deployment package.I guess I could copy the WinSCP script to an external file path, but I'd prefer to keep them together for version control if possible.

View 0 Replies View Related

Running SSIS Packages In MS Server 2005

Jan 31, 2008

Hi
Just a brief question,
is there a way of displaying a success or not success message when running (testing) an SSIS package on SQL 2005 ?

Because i have to go through the steps to see if there was an error or not and for large SSIS packages this is a pain.

With DTS 2000 after it ran you got a message saying successfull or not, is there a way to activate this in SQL 2005 ?

I suppose i could just run the job ... but I prefer the other way as sometimes a job fail is a permissions problem, not a SQL/datapump task problem...

View 1 Replies View Related

SQL 2012 :: Identify Whether Files Are In Read / Write Or Read Only

Mar 24, 2015

How to identify whether the files are in read write or read only?

View 1 Replies View Related

Regarding Running Batch Files And Different Packages

Mar 19, 2007

I have created a master controller package which runs as follows

deletes all the log files -> deletes few flat files on different drives -> preprocess task(execute package task) -> c# executable (execute process tasks) -> postprocess tasks (execute package tasks)

i need a create a task just before the preprocess task with an user input asking whether he wants to run a particular batch file before proceeding to preprocess. if the user says yes it should run a batch file followed by preprocess tasks, c# and post process or else it should directly goto preprocess, c# and post process (neglecting batch file task)

can anyone help me how to do it.

View 9 Replies View Related

SSIS Running Packages

Sep 20, 2006

Is there any way to check from SQL code if a specific SSIS stored package is running like a SQL system view or stored procedure? Management studio option works fine but not if you need to determine if a specific package already is running or not?

View 3 Replies View Related

SSIS Running Packages NOT Being Displayed

Sep 5, 2006

When I run an SSIS package from the MSDB in SQL Server Management Studio it never shows up under 'Running Packages'.

I have confirmed that the package is indeed running.

I've also tried to check the packages running on the server programatically by using the GetRunningPackages() method. This too returns nothing when clearly packages are running.

Any ideas?

Malcolm Barclay

Disney Consumer Products UK

View 4 Replies View Related

SQL Agent Running SSIS Packages

Apr 18, 2006



Hi there,

if I have some packages (.dtsx) that are depolyed to Server A and I have SQL Agent installed on Server B - if I run a run a package from SQL Agent, does it run on Server A or Server B?

Thanks in advance.

View 1 Replies View Related

Monitoring The Running And Execution Of SSIS Packages With MOM

Jan 11, 2006

Hi,

Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?

What is the recommended way to monitor SSIS execution?

Thanks,

- Joel

View 2 Replies View Related

Inconsistent OLE DB Failure When Running SSIS Packages

Jan 24, 2007

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @[User::ConString] , "Royalty", @[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.



I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto



Any ideas welcome



View 5 Replies View Related

Running SSIS Packages In Management Studio

Mar 18, 2006

We are using SQL Server 2005 Standard edition. We have the development studio installed on a developer€™s workstation. The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.

The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server. I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.

When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.

I€™m sure this is a permission based problem since it runs fine on my workstation. I tried logon onto the SQL Server with my id (I€™m an admin to the server). I tried running the agent with my windows id. It looks like I€™m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.

In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.

View 4 Replies View Related

Running Remote SSIS Packages Dynamically

Dec 19, 2007

I seem to be missing something with SQL 2005 SSIS and I am wondering how I would accomplish my taks. With SQL 2000 and DTS we designed our DTS packages so that we could load one file or database from start to finish. We then have controlling programs written in VB that will monitor directories, when a file shows up in a directory it loads the package, changes the necessary properties and executes it. These programs handle all of our error notifications and dealing with the files afterwards, making sure files are complete in the directory before the DTS runs, etc. With .NET and SQL 2005 I have learned that I can change the properties by using variables and expressions, which I think would handle all of our situation but I can not run them remotely. The documentation says that I could setup a scheduled job and then call sp_start_job from my program. That seems like it would require a lot of scheduled jobs and then I still need to get the parameters into the packages. The other option is to use a web service, that would require IIS on my SQL box which everywhere says I don't want. Am I using SSIS for something I am not supposed to be or how do I accomplish this in 2005?
I have noticed that you can not even run packages through the SQL Server Management Studio. We have a number of packages that are built that are run on demand when needed. In SQL 2000 I could just click and execute the package. In 2005 is my only option to setup some sort of fake job in the job scheduler?

View 6 Replies View Related

SQL Agent Job Fails Running SSIS Packages

Oct 16, 2006

I need some help from you on a problem with SQL Server Agent that I believe many of you have experienced. When searching on the web I find some loose answers, but it would not solve my problems. So here is my story.

1. I have created two SSIS packages, package 1 and package 2. Both packages have a Execute SQL Task making a select sql statement to a table, say Customer in AdventureWorks DB. Package1 also makes a Execute Pacakge Task to Package 2. Both packages have protectionlevel property set to EncryptSensitiveWithUserKey

2. Then I created two package configuration files per package, one indirect xml configuration and one xml configuration file. The former I created an environment variable called pack1, which was specifying the path and file name of second config file. Likewise, I did for package2. Maybe I could have reused thie first configuration file, but I am not sure since package1 calls package2.

3. Now I build and deploy my packages. Then I run my manifest file, going through the deployment wizard setting the necceccary properties like server name, user, password, I have also create the same environment variables on the deployment machine. I ensure that the package1 and package2 can be run under integration services (file system). And both package works. So far so good

4. Now I want to create a SQL server agent job that runs package1 and package2. Here I found an article that decribed How to Schedule and Run a SSIS package ( DTS ) Job in SQL Server 2005 http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

5. I created a job executor account and then Create SQL proxy account and associate proxy account with job executor account as described. I created a new ssis job, specifying type ssis integration services, run as proxy, setting package source to SSIS package store, naming the server and package name (package2).

6. Now, I try running job1 which runs package2. ANd it works. I was aware if I needed to give file permission on users (read and execute) on my dtsx/dtsconfig/log files. So far so good

7. Now, I try to run job test which runs package1 (which also runs package2). But now the job fails :-(. The job runs for some seconds, but fails. I have to say that I have some script tasks with message boxes inside!!! hope this doesn't matter. I have created all neccessary accounts, proxies, credentials and so on. here is the view history

Date 17.10.2006 00:50:53
Log Job History (test)

Step ID 1
Server ABEO-GEIR
Job Name test
Step Name test
Duration 00:00:07
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: abeo-geir
as_jobuser. The package execution failed. The step failed.



Continue: This error is well known for you. It has been mentioned in many postings. Well, how do I go from here. Setting logging on the job results in only this information

PackageStart,ABEO-GEIR,ABEO-GEIR
as_jobuser,Package1,{3F5BD166-D24B-4838-B3F1-018B6C2C7523},{8E1B8E45-7F1D-4C24-852C-2694F993E6D2},17.10.2006 00:59:40,17.10.2006 00:59:40,0,0x,Beginning of package execution.

PackageEnd,ABEO-GEIR,ABEO-GEIR
as_jobuser,Package1,{3F5BD166-D24B-4838-B3F1-018B6C2C7523},{8E1B8E45-7F1D-4C24-852C-2694F993E6D2},17.10.2006 00:59:41,17.10.2006 00:59:41,1,0x,End of package execution.



Well, I read some place that I could run the job as type operating system (cmdexec). Here i could not choose Run as, but had to select SQL SErver agent account. NOw I got this message

Date 17.10.2006 01:05:21
Log Job History (test)

Step ID 0
Server ABEO-GEIR
Job Name test
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (test).



As you see, I can't come any further. I NEED HELP FROM YOU. I AM STUCK. I know many of you have solved this and I hope you can guide me through thise. I don't know how many hours or days I have used on this.



Regards

Geir F

View 2 Replies View Related

Errors While Running Ssis Packages From The Command Prompt.

Dec 23, 2005

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
   Code: 0xC00470FE
   Source: Data Flow Task DTS.Pipeline
   Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
   Code: 0xC00470FE
   Source: Data Flow Task DTS.Pipeline
   Description: The product level is insufficient for component "Script Component" (9).
End Error


i have entered the command as follows.

dtexec /f "c:somefolderpackage1.dtsx"

 

Any points regarding this issue would be helpful.

 

View 7 Replies View Related

Usage Question - Running SSIS Packages Remotely

Jan 29, 2007

Hello,

I currently use DTSRun to run packages on my scheduler box. I would like to use the same set up to run SSIS packages.

Is there any way to duplicate this without installing SSIS on my scheduler box? Can I specify the server on which it should run?

Just don't want topurchase another license, if possible.

- Dave-

View 3 Replies View Related

Scheduled Job Running SSIS Packages Not Completing As Expected

Oct 2, 2007

I have a SQL Scheduled Job in SQL 2005 SP2 that executes a few different SSIS packages. When I update the SSIS Packages, SQL Agent runs the job and shows success; however, the underlying packages are not executed. The packages are located on the server but we are using UNC paths in the jobs since much of the time they are started manually from a remote workstation and the jobs seem to look at the initiating workstation's file server rather than that of the SQL Server. Is this a known issue?

Thank you,

Jennifer

View 7 Replies View Related

Cloud Of Confusion: Running SSIS Packages On 64 Bit SQL Server

Sep 25, 2007

It is clear to me that in order to be able to use certain SSIS components (for example the Excel jet provider) I must launch my packages using the 32bit DTEXEC located at Program Files (x86)Microsoft SQL Server90DTSBinn. However, when I do this it seems that there are other components of the package that no longer work as expected.

To test this I have created a simple package with two tasks (Run64BitRuntime is set to False):
1. Data flow task importing data from Excel
2. Execute SQL Task which does a simple select (select 1) from a Native OLE DB SQL data source (same SQL Server on which packages are stored). This task contains no input or output parameter.

When I try to execute the package using the 64bit DTEXEC, task 1 fails with the following error (as expected):
Code: 0xC0202009
Source: connection1 Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

When I execute the package with the 32bit DTEXEC, task 2 fails with the following error
Code: 0xC002F210
Source: Execute SQL Task Execute SQL Task
Description: Executing the query "" failed with the following error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Now here is the confusing part: When I change task 2 to use the .Net provider instead of the OLE DB the package works fine. According to the MS documentation, both of these providers are supported on 32 and 64 bit so am I missing something? One more thing to note: before I was able to use the 32 bit DTEXEC I had to re-register it as described in this KB article: http://support.microsoft.com/kb/919224

View 5 Replies View Related

Running SSIS-packages In Stored Procedure Using Dtexec

Apr 2, 2008



Hi,

I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.

The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.

Here is a sample list of ssis-packages from procedure:


exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataaaa.dtsx"'

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedatabb.dtsx" '

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataccc.dtsx"'

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataddd.dtsx"'

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataeee.dtsx"'


So, if eg bbb.dtsx would fail, is it possible to get the whole procedure failing?

View 5 Replies View Related

XML Direct Configuration Files For SSIS Packages

Nov 16, 2006

Hello,

I need some clarification. I am trying to utilize the XML Direct Configuration in my SSIS packages to utilize database connections in the package. I am wanting to utilize this the same way you could use UDLs in the SQL Server 2000 DTS packages.

Currently, I am creating the dtsConfig file and saving it to my desktop. I then modify it with notepad and add the following configuration where it looks like this:

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="Package.Connections[ConnectionName].Properties[ConnectionString]" ValueType="String">




<ConfiguredValue>Data Source=[ServerName];Initial Catalog=[DBName];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Once this is created, I am trying to "re-use" this in SSIS packages created moving forward, where they all point to this configuration for the same database connection.

What I don't understand is when "Enabling Package Configuration" and then pointing to this dtsConfig file doesn't create a connection in "Connection Manager" NOR does it provide a way to create a blank connection and point to this configuration.

I feel like I am missing something thats so "great" about XML configuration files. Any help would be appreciated.

Thanks,

Daniel Lackey
MCSD

View 3 Replies View Related

Behavior Of Ssis Packages Running Under SSMS Manually Vs. Job Agent Automatically

Sep 5, 2007



I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is
"An out-of-memory condition prevented the creation of the buffer object. "


Anyway, the package runs successfully when it runs automatically under the job agent.

I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.

js40

View 2 Replies View Related

SSIS Packages That Load Different Text Files Into Tables

Jan 25, 2013

So i have about 80 different SSIS PAckages that load different text files into tables. each package has a config file that contains password, server name and user id. I want to forward these packages to a use who will execute them. So instead of giving the user 80 packages to execute manually, how do i put these packages into one parent package so that the user has to only execute this parent package instead of executing 80 different packages NB. package exec is manually. (we will automate later) so this is a temp measure.

View 2 Replies View Related

Is Ssis The Only Technology That Can Read/write Raw Data Type Files?

Sep 21, 2007

I'm contemplating a sql server archive strategy that rolls really old data off any sort of dbms and onto low cost media like dvds in a non relational archive format. I dont want to ever worry about these archives spanning different versions of sql when i go to retrieve a range of data that happens to span sql versions (eg one disc was sourced from 2005 another by 2008 but my report needs a union of both).

So I'm thinking about neutral/efficient formats for these archives and a live homegrown catalog that can determine exactly what disc(s) need to be mounted based on passed from and to date parameters...all so that the data that might span discs (and versions and maybe even schemas) can be merged and loaded into my sql version d'jour's "throw away" archive database for a one time report or other unplanned activity.

I remember raw data types being very convenient as an ETL format for our customers who have ssis, but wouldn't want our sqlexpress customers to be left without the archiving capability. Do the "things" that read and write raw data files really originate in some special T-SQL command that all sql editions can use, or is it strictly an ssis thing?

View 4 Replies View Related

Running Bat Files In SSIS

Aug 27, 2007

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

View 13 Replies View Related

Integration Services :: SSIS Project - Read Multiple Flat Files With Different Formats

Jan 15, 2014

I need to import multiple flat files with different formats into different tables of the sql server database and not able to figure out the best way out in ssis to do so...

What are the possible methods in ssis to do so and if possible the process which can be dynamic as file names or columns might change in future.

View 7 Replies View Related

Integration Services :: SSIS Package Which Read Excel Files Fails On Server Agent

Oct 19, 2010

I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

View 14 Replies View Related

Running A Large Number Of SSIS Packages (with Dtexec Utility) In Parallel From A SQL Server Agent Job Produces Errors

Jan 11, 2008

Hi,

I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.

I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.

The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.

The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).

We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).

I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.

In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).

If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.

The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.

Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.

I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).

Additional information:

The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.

The request:

Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!

Eero Ringmäe

View 2 Replies View Related

SQL 2012 :: How To Add Variables To SSIS Packages Per Batch

Nov 18, 2014

I am using Variables to configure a lot in an SSIS Packages.

Over the years you add new variables that are usefull to your default-package.

However to use those in "older" packages you have to open add them manually.

Any way (e.g. skript) to add a set of variables to every SSIS-package in a folder ?

View 3 Replies View Related

SQL Jobs Calling SSIS Packages Within MSDB Not Referencing Connection Info In Config Files

Nov 13, 2007

I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:

Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.

As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.

Any suggestions? Thanks.

T

View 3 Replies View Related

SQL Server Upgrade 2000 - 2005 Files Read Only But Not Really

Feb 28, 2006

Hi,

I'm trying to upgrade from SQL Server 2000 to 2005. The problem I am having is that when I try to attach the existing db files I get a message that says "database cannot be upgraded because it is read only or has read only files...."

Thing is... there is no write protection on the files.

Can anyone advise me on how to overcome this problem so that I can attach the db, please?



Thank you

Robert

View 25 Replies View Related

SQL 2012 :: Refreshing MSDB SSIS Packages From One Server To Another

Mar 25, 2015

I was recently tasked with creating an automated process to refresh SSIS packages from MSDB on one server to another and I decided to go the route of using Powershell, however this wasn't as straight forward as I originally imagined so I thought I would share my solution in case others encounter the same request. The below PowerShell code will create all Integration Services folders from the source MSDB (that contain SSIS packages) on the target instance of MSDB then copy all packages to the proper folder locations. The /QUIET option is used to automatically overwrite packages that already exist on the target so make sure you want to overwrite the current versions of your packages before executing.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-NULL

$app = New-Object Microsoft.SqlServer.Dts.Runtime.APPLICATION
$SourceServer = "<source server name>"
$TargetServer = "<target server name>"

$Packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SourceServer -Query "

[Code] .....

View 0 Replies View Related

SQL 2012 :: Dynamically Calling Child SSIS Packages Within A Project

Mar 9, 2015

I've not really used SSIS for a while, and I'm now building some in 2012 and trying to utilise some of the features in the 2012 SSIS catalogue; however I've hit a bit of a stumbling block.

What I'm trying to do is have a master/child package relationship, with several child packages and where the child packages themselves are dynamically called (i.e. the master package may call a different child package based upon some value or state of data already processed.)

When I try and create an expression for the PackageNameFromProject property... well, that property doesn't appear to set dynamically. I know how to do this for old style packages by creating expressions for the package name etc; but that way I can't use the package parameters I have from my master package.

View 2 Replies View Related

Integration Services :: Deploy SSIS Packages 2012 And Run Job Through Powershell

Jun 1, 2015

Any script  in ssis 2012 packages deployment  and create the jobs though power shell script.

View 3 Replies View Related

SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS

Nov 11, 2014

I have an excel file which has dynamic columns

i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

My problem is to Unpivot the data

Date 8/2/2013 8/9/2013 8/16/2013

Stock 1,561 1,661 1,761

i.e. the abobe table should become as

Date Stock

8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761

How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved