I have to import 16 Excel files into 16 tables through agent. The agent will process the data further. I have created a SSIS package for each . I don't want to create 16 jobs to do same steps. My idea : 1. Pass the SSIS package to Job (Looping the path in procedure and call the job) 2. Once the data is imported , on success / failure : different steps will be processed . 3. Once success , the next Package will be processed (Step 1) Can this be done without Bulk Insert

We have migrated a SQL 2000 server to VM and I am wondering what changes will need to be made on the VM? After the VM migration an SSIS package that transferred data from the 2000Server to a 2008Server will not connect. Nothing changed on the 2008Server but none the less the SSIS connection fails to authenticate. I can log in remotely from the 2008Server to the 2000Server using the credentials in the SSIS package. So, is there anything that needs to be configured on the VM side that should be configured? I have checked services, account privileges, etc. The two servers can communicate, ports are open, osql -L and sqlcmd -L see the servers. Nothing on the VM server has changed from the physical server configuration wise.

I have a task that should rename a file I have a variable called RenameFileName \\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv When I run the task I get the error Illegal characters in path Am I doing anything wrong? Below are a list of the variables I have tried \\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv \\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv "\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv" "\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv" Please note that the following works: \\\\servername\\csv\\BACKUP\\ABC.csv This however does not work \\\\servername\\csv\\BACKUP\\"+1+".csv It errors as soon a I put in double quotes. Single quotes are fine and they get put in the filename Thanks

I am trying to use an Oracle OleDb source in my SSIS package with no luck, every time I try to connect to the server I get the error: >ORA-12154 tns could not resolve the connect identifier specified , knowing that: 1 I installed Oracle 11g express edition 2 I added an entry for the alias in my tnsnames.ora file 3 ORACLE_HOME variable pointing to my Oracle installation 4 Path & TNS_ADMIN variables pointing to the directory containing the tnsnames.ora file 5 I am using a Windows Server 2012 What am I doing wrong?

In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source. For example, suppose I have a table called mytable with 5 columns (column1, column2, column3, column4, column5) but I'm only interested in column1. I see these 2 options: 1. I can either use "Table or view" and select "mytable" and then only check "column 1" from the "Columns" pane. 2. Or I can use "SQL Command" and use this query "select column1 from mytable". When I ran a test, the second option seemed to run much faster but I'd like confirmation of what's going on behind-the-scenes when I use "Table or view" and then only select certain columns. I'd appreciate any insight anyone can provide. (In my particular use case, mytable is an offsite Azure Database table and column2 has large amounts of xml within it. My guess is that all of the columns were coming over the wire when I used "Table or view")

To set the scene, I maintain 4 local SQL Server 2008 R2 instances. At some point in the near future, 3 of these will be upgraded to SQL Server 2017 and migrated to azure. I am currently using SSMS and SSIS 10.5 to manage these servers and develop ETLs. Can SSMS 10.5 and 17.7 be run side-by-side, and can I do this before we've even upgraded any of the instances to 2017? Is there any benefit of doing so? I'm considering forking out for ApexSQL but some of the tools aren't compatible with 10.5. Can SSDT also be installed side-by-side with SSIS 10.5? Thanks.

I have an SSIS package that runs a number of **Data Flow Tasks** all at the same time. Each **Data Flow Task** is placed inside a Sequence Container. Before the **Data Flow Tasks** are run, a **Script Task** is used to check what **Data Flow Tasks** are required to run. Each **Data Flow Task** also has a related variable, which is set to true or false by the **Script Task**. The Data Flow Tasks use the variable value as a *Disable Expression* property. When run initially, the process works as expected. Those Tasks that have variable value = true do run, and those that have a variable value = false do not run. However, the next time I go to run the package all **Data Flow Tasks** are disabled initially (probably because the related variables are set to false). But even when some variables are then set to true at runtime, the Tasks remain disabled. The *Disable Expression* does not seem to pick up the latest variable value. (in the Watch window I can see the variable values have updated correctly) I tried setting '*DelayValidation*' *= true* on each Task but no change. Any reason why this happens?

I have developed a SQL Server Integration Services package pulling data from Oracle 11g and storing in a SQL Server 2008 database. Everything works as expected, but when I schedule a sqlserver job to run the SSIS package it returns the following error: > Error: System.InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset) at System.Data.OracleClient.OCI.DetermineClientVersion() --- End of inner exception stack trace --- at System.Data.OracleClient.OCI.DetermineClientVersion() at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OracleClient.OracleConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) Please note this error occurs only while running the package as a sqlserver job. If I run the package via Integrated Services in SQL Server Management Studio then I don't get any error. I have even installed both 64-bit and 34-bit Oracle driver but no luck :( Please provide inputs.

Recently I have buffer issue for my SSIS package. When i started to monitor metrics in perfmon i noticed the PLE is nearly 500000. Is it ok to have this huge PLE.

I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so the run duration has increased and I would like to know if there is a way I can find out where the job is spending more time and what it is waiting on either running a profiler trace or running a tsql script inside a job to capture anything useful. It runs every day except Sat at 11p est.