Load multiple Excel files into multiple SQL database tables using SSIS

I need to load multiple individual Excel files into multiple SQL tables in the same database using SSIS.

I have 61 files need to loaded into 61 tables in the same SQL database.

I created tables in SQL database.

I created SSIS project for Data Flow Task, Foreach loop container specification , Variables for connectivity as the picture below.

Data Flow control has:

Excel Source:

OLE DB Destination:

The problem is this way read all the files in the folder but add it to one table in the database.

I need to add each file to his independent table in the SQL database.

Thanks for helping.

Adding counted rows to running number in UPDATE

I am trying to UPDATE usernames to our database, but I have problem with UQ.

In our schema we have corporate admin users and contacts in the same table. Every corporate has 1 admin user which where composed by taking 6 characters from their name and adding running number (if there were 6 character duplicates).

For example:

CompanyName: Test Company
UserName: testco-1

Running number with admin users (with the same 6 character start) varies from 1 to 15(ish).

Our contact table has column CorporateAdminId which is set to NULL with Admin users, but contacts are referred to admin user with this field. So the table has a relationship to itself.

NOTE: Before contacts did not have usernames Because software upgrades our contacts need to have username also. Usernames to contacts are created with the same rule (6 char+running number) and the first 6 characters are defined by AdminUserId reference (not the contacts own corporate name)

For example:

AdminUserId: GUID (Refers to Test Company)
CompanyName: Data miner
UserName: testco-2

My problem here is that how can I count how many usernames there are in the database at the moment that start f.e with 'testco' and add this number to the running number that I use to create contact usernames (so there will be no collissions with UQ)

SQL : stored procedure won't return any error message or number

Here is my insert stored procedure:

CREATE PROCEDURE spainsert 
    @A_Name nvarchar(50) 
AS BEGIN 
   BEGIN TRY
      DECLARE Detailed_Error VAR_CHAR(MAX); 

      Insert into A(A_Name) 
      values(A_Name) 
   END TRY 
   BEGIN CATCH
       SELECT 
          Detailed_Error = CAST(ERROR_NUMBER() AS VAR_CHAR ) + ' : '+ ERROR_MESSAGE() + ' : ' + CAST(ERROR_LINE() AS VAR_CHAR );
   END CATCH 
END 

When I try to enter NUMBER instead of NAME : exec spa insert 500, it won't show any error, but just saves that row into table. Could you please help me how to handle errors, how to know the number and the error message ?!

Thank you all ! 123456787523535464654645654651541541255435.4153241653241636524165324135

SQL Server float data type

I started this thread SQL Server float data type understanding

And the following question is partially related to it, so I bring it up back here.

Documents and SQL Server users say that float(8 bytes) and real(4 bytes) data types are for approximate use only. I can see that via use of SQL Server Management Studio and some simple queries like

declare @f float=0.000123456789123456789;
select @f;

where the output becomes 0.000123456789123457 (auto round-off error) as the precision is set to 15 non-zero digits and after the decimal point.

I think the output by SQLServer Management Studio is implementation defined. Because the byte series fed into the system is transformed into a long bit integer then cast into a float type of the same precision depending upon the language being used to implement the system and the casting functions. If the main cause lies right at this point then I think the approximation issue can be fixed. That is to reprogram the functions with data streaming to get back the exact values as previously input.

However, I still wish to learn what other particular examples you know about that can exhibit their approximate error symptoms ?

I still do have another question mentioned also in my previous thread about the byte order and its transformation of float and real data type when it is being processed in either big and little endian systems. If someone could offer me some ideas, it would be really awesome. My take on this is that float is fixed on little endian, real is not an exception because they are different only in the storage. Their functions are totally the same. Yet confirmation from experts over this is badly needed.

SQL find missing language entries in table

I have a table which is missing some entries for a certain language. How can I get a list of all language text in english (lang 1 in table) which is missing the foreign translation counterpart (lang 2)

My table is as follows

PageName | LanguageNo | TranslationName | TranslationText   |
main     |     1      | SomeName        | some english text |
main     |     2      | SomeName        | some foreign text |
main     |     1      | SomeName2       | some english 2    |
other    |     1      | SomeName3       | some english 3    |
other    |     2      | SomeName3       | some foreign 3    |

For example, using the above table data, only the following should be returned:

main     |     1      | SomeName2       | some english 2    |

How can I write a SQL statement to achieve this?

Thanks

SSIS Balanced Data Distributor 2014 SP1 for VS 2012 doesn't work

I'm trying to install SSIS Balanced Data Distributor 2014 SP1 for Visual Studio 2012, but this component is not displayed in the SSIS Toolbox.

I install x86 version of this component. The TxBDD.dll is located in the directory correctly: C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents

How can I fix it?

Thanks!

How to convert code from SQL Server to Oracle

I wrote this code in SQL Server. I want to write this code for Oracle and PL/SQL. Could anyone help me how I can do this job?

SQL Server / T-SQL:

ALTER TRIGGER [dbo].[checkBalance]
   ON  [dbo].[Orders]
FOR insert
AS
BEGIN
    IF (SELECT goodcount FROM goods WHERE id=(SELECT gid FROM Inserted)) < (SELECT gcount FROM Inserted)
    BEGIN
        RAISERROR ('Inventory is low',10,1)
        rollback
    END 

How to insert value in NULL from another corresponding row?

How to join for 3 tables, so that some null values in result, can be replaced.

I tried this:

SELECT        ISNULL(Culture,CultureInfoCode) as Culture, ISNULL(Name,'NA') as Name,  ISNULL(Value,'NA') as Value
FROM            SiteLanguages Left JOIN
                         WebDbResources ON SiteLanguages.CultureInfoCode = WebDbResources.Culture Left JOIN
                         KeyWebDbResources ON WebDbResources.KeyWebDbResourceID = KeyWebDbResources.KeyWebDbResourceID
                         where Status=1

Here is the result

Culture  Name                                                 Value
en-US    Key_FirstName                                        John
TR       NA(Here It should also be Key_FirstName)             NA (ok)

But I want Name field in second row as Key_FirstName and Value NA (For corresponding TR Culture).

Here is the schema:

SiteLanguages:

CultureInfoCode Status
en-US            1
TR               1
AR               0

KeyWebDbResources:

KeyWebDbResourceID  Name
1                   Key_FirstName

WebDbResources:

WebDbResourceID  KeyWebDbResourceID   Culture   Value
1                   1                  en-US    FirstName

Second translation does not exist for TR, that's why I want to Fill null on join having Culture and key.

Published to Azure VM with MSSQL, SQL DELETE not working

Published my ASP.net finally as i was knew to it and had a few things to sort out. But I managed it.

The page runs fine, and the SQL server saves data, pulls the data for the next session, but it will not delete the data.

This only happens on the Azure VM, locally it works fine without any problems.

Im thinking its probably some sort of setting?

Does anyone know what can be wrong.

Thanks

how to export transparent data to encrypted database in sql server

I want to move/copy transparent data from a database to another database having same tables structure but encrypted. How can i do this job?

How to run to a sequence from stored procedure?

I am trying to run a sequence from a stored procedure. I am passing the name of the sequence to the stored procedure and the stored procedure will return the sequence value, but the stored procedure is not recognizing the passed sequence name. the error says:Incorrect syntax near '@SeqName'.

Here what I have tried:

ALTER PROCEDURE [dbo].[GetSeqNextValue] (@SeqName varchar(50), @NewNum bigint output) 

AS

BEGIN
          SET @NewNum = NEXT VALUE FOR @SeqName
END

SQL Server connection in Wildfly using JTDS driver

What is the correct way to setup a SQL Server datasource on Widlfly?

I need to access a SQL Server database from my web application which runs on Wildfly.

I have setup the datasource as follows:

<datasource jta="false" jndi-name="java:jboss/db" pool-name="db" enabled="true" use-ccm="false">
    <connection-url>jdbc:jtds:http://sqlserverIP_ADDRESS;instance=SQLEXPRESS;DatabaseName=DB</connection-url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <driver>jtds-1.3.1.jar</driver>
</datasource>

This works fine except that when the SQL Server is restarted, the connection is lost and the datasource doesn't manage to recreate one. So I get errors like:

Invalid state, the Connection object is closed.

This post suggests adding some validation, so I did this:

<validation>
    <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
    <validate-on-match>false</validate-on-match>
    <background-validation>false</background-validation>
</validation>

But that does not solve the problem and I still get the same "connection closed" error from time to time.

This other post suggests using a DataSource instead of a Driver, so I have added this to my configuration:

    <datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</datasource-class>

But when I test the connection I get an exception:

java.sql.SQLException: The serverName property has not been set.
at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:150)

In mssql: when I SUM values of each month between 2 dates, I get the wrong value

I am really struggling with a query. I want the sum of each month between Aug 2014 and July 2015. If I specify the between dates in the where clause it sums all the months.

Here is my query:

DECLARE @CurrentYear int = DATEpart(year,getdate()) 
DECLARE @PreviousYear int = DATEpart(year,getdate()) -1
SELECT  
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'08') THEN a.balance ELSE 0 END) AS BalanceAug ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'09') THEN a.balance ELSE 0 END) AS BalanceSep ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'10') THEN a.balance ELSE 0 END) AS BalanceOct ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'11') THEN a.balance ELSE 0 END) AS BalanceNov ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'12') THEN a.balance ELSE 0 END) AS BalanceDec ,
...etc.
FROM subaccount_history a with (nolock) 
WHERE fin_period between concat(@PreviousYear,'08') and concat(@CurrentYear,'12')

The issue is with the between clause, i tried group by but that also doesn't work. It sums everything between the 2 dates specified.

SQL SERVER - Return rows in stored procedure

For starters, I'm fairly new to SQL.

So, I am trying to develop an application in asp.net with blogs and categories in two languages. One of the functionalities is returning the name of a category in a particular language. Not all categories have two languages. Some have English, others have Italian, others have both. Here's the function that performs the select i was talking about.

DECLARE @Name NVARCHAR(250)
IF EXISTS(SELECT Name = Coalesce(BlogCategoryTranslation.Name, ' ')
FROM BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID AND LanguageID=@LanguageID)
BEGIN   
    SET @Name=(SELECT Name
    FROM BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID AND LanguageID=@LanguageID) 
END 

ELSE
BEGIN
    SET @Name=(SELECT top 1 Name FROM
    BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID AND LanguageID=@LanguageID);
END

RETURN @Name

Now I have a stored procedure that returns the Category Name for a given language. What I want is to always return a Category. If it doesn't exists in a particular language, i want to display the record in other language. Is this possible and if it is, what would be the suggestions in order to do that.

ALTER PROCEDURE [dbo].[BlogCategoryLanguage]
    @BlogCategoryID INT,
    @LanguageID INT
AS
BEGIN

SELECT BlogCategoryID AS 'CategoryID',
    Language.Name AS 'Language',
    dbo.BlogNameCategoryByLanguage(1,1) as 'Name',
    dbo.Blog_PublishedInCategory(1,BlogCategory.ID) AS 'Published'
FROM BlogCategoryTranslation
INNER JOIN BlogCategory 
        ON BlogCategory.ID = BlogCategoryTranslation.BlogCategoryID
INNER JOIN Language 
        ON Language.ID = BlogCategoryTranslation.LanguageID
WHERE BlogCategoryID = 1

I would really appreciate a few tips. It's the first time i have posted a question here and I'm not quite sure how this works. If this is a repost somehow, sorry for that.

Why am I getting a deadlock when my code is accessed multiple times?

In my c# code I have the following method that creates a document in the database, adds metadata regarding the document to the database and then updates some information regarding the date the repository was last updated. This method is often called numerous times in quick succession as multiple file uploads are common. However I am having problems with the code failing due to deadlock in sql server.

private IEnumerable<DocumentMetadata> CreateDoc(int? jobId, int?repositoryId, int? folderId, string documentTypeString,       IEnumerable<DocumentModel> files)
{
    if ((jobId == null && repositoryId == null) || (jobId != null && repositoryId != null))
        {
            throw new InvalidOperationException("Either job id or repository id must be specified");
        }
    using (var tran = new TransactionScope())
    {
        List<DocumentMetadata> newDocuments = new List<DocumentMetadata>();

        var documentType = GetDocumentTypeByPrefix(documentTypeString);

        if (folderId == null)
        {
            // Find the root folder
            var job = getJob(jobId);
            var rootFolder = getRootFolder(job);

            // If we can't find a root folder, create one
            if (rootFolder == null)
            {
                rootFolder = CreateRootDirectory(job);
            }

            folderId = rootFolder.FolderId;
        }

        User currentUser = _userService.GetCurrentUser();

        foreach (var file in files)
        {
            var document = new Document() { Document1 = file.Data };
            var documentMetadata = new DocumentMetadata
            {
                Document = document,
                CreatedDate = file.CreatedDate,
                FileName = file.Filename,
                FileSize = file.Data.Length,
                FolderId = folderId,
                DocumentType = documentType,
                JobId = jobId,
                RepositoryId = repositoryId,
                User = currentUser
            };

            _unitOfWork.DocumentMetadata.Add(documentMetadata);
            newDocuments.Add(documentMetadata);
        }

        // set repository updated date 
        if (repositoryId != null)
        {
            DocumentRepository repo = GetDocumentRepository(repositoryId);
            if (repo != null)
            {
                repo.UpdatedDate = new DateTimeOffset(DateTime.Now);
            }
        }

        _unitOfWork.SaveChanges();
        tran.Complete();

        return newDocuments;
    }
}

After some debugging it would appear that the updating of the repository id is causing the deadlock problem. If I remove this code block outside of the transaction all files are saved with no errors.

Why would this code block

if (repositoryId != null)
        {
            DocumentRepository repo = GetDocumentRepository(repositoryId);
            if (repo != null)
            {
                repo.UpdatedDate = new DateTimeOffset(DateTime.Now);
            }
        }

cause the deadlock? No other access is being made to the DocumentRepository table apart from in this method - as the locks are obtained in the same order surely there should be no deadlock?

What is it about this code that is leading to deadlock?

Updated: The code for GetDocumentRepository is:

 public DocumentRepository GetDocumentRepository(int repositoryId) 
 { 
     var result = DocumentRepositories.SingleOrDefault(x => x.RepositoryId == repositoryId); return result; 
 }

Ensuring the database connection opens and closes every time I use Dapper to access the database

Here is what I am currently doing in one of my repository classes:

private IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString);

public IEnumerable<Product> GetProducts(int categoryId = null, bool? active = null)
{
    StringBuilder sql = new StringBuilder();
    sql.AppendLine("SELECT * ");
    sql.AppendLine("FROM Product ");
    sql.AppendLine("WHERE @CategoryId IS NULL OR CategoryId = @CategoryId ");
    sql.AppendLine("  AND @Active IS NULL OR Active = @Active");

    return this.db.Query<Product>(sql.ToString(), new { CategoryId = categoryId, Active = active }).ToList();
}

One thing I want to do is put the IDbConnection property in a BaseRepository that all of my other repos inherit from. What do I do to ensure my database connection opens and closes properly in each of my data access functions like the example above? Here is what I currently do with Entity Framework (w/ a using statement around each function, but now I am switching the DAL to use pure Dapper:

using (var context = new MyAppContext())
{
    var objList = (from p in context.Products
                   where (categoryId == null || p.CategoryId == categoryId) &&
                         (active == null || p.Active == active)
                   select p).ToList();

    return objList;
}

I noticed in the Dapper examples that everything is wrapped in a using statement like I would expect, but occasionally I see them wrapping their functions in the follow using:

using (var connection = Program.GetClosedConnection())

GetClosedConnection() returns a new SqlConnection, but what is the difference between the two?

public static SqlConnection GetOpenConnection(bool mars = false)
{
    var cs = connectionString;
    if (mars)
    {
        SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(cs);
        scsb.MultipleActiveResultSets = true;
        cs = scsb.ConnectionString;
    }
    var connection = new SqlConnection(cs);
    connection.Open();
    return connection;
}
public static SqlConnection GetClosedConnection()
{
    return new SqlConnection(connectionString);
}

Truncate selected tables in SQL Server

How to truncate selective tables in SQL Server 2008, I have a list of tables which may be excluded during truncate process.

Anybody can guide?

Date wise optional parameter searching in SQL Stored Procedure

I want to take a leave report from my leave application table when I search. In the table I have Leavefrom(datetime), LeaveTo(datetime) columns. Now I want to take the rows on the basis of these two columns. My searching parameters are nullable they are @employeeid, @datefrom, @dateto.

I need to get the result must between the date of Leavefrom, LeaveTo. I am trying to make a stored procedure for this.

ALTER PROCEDURE [dbo].[SP_GetSpecificLeaveReport]
@empid int=null,
@leavefrom date=null,
@leaveto date=null
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ela.appliedDate,ela.appliedBy,ela.leaveFrom,ela.leaveTo,ela.noOfDays,
    ejd.firstName,ejd.lastName,
    ltm.leaveType
    from dbo.tblEmployeeLeaveApplication as ela inner join dbo.tblEmployeeJobDetails as
    ejd on ela.empId=ejd.recordId inner join dbo.tblLeaveTypeMaster as ltm 
    on ela.leaveTypeId=ltm.record_Id where



END

SQL Select statment with remove first row

I have multiple rows in table, I'm using select statement to fetch the details.

select * from pdetails where pcode='P000437' 

Result

my query print 3 records but I don't want to print first row.

Is there any option to use select statement with condition to count and remove first row.

Query is giving invalid column error

I have a query which is giving me error as

Column 'designation' does not belong to table Table.

Here is my query:-

Select  upper(ra1user.first_name +  ' ' + ra1user.last_name)  RA1_Name, " +
                       "ra1user.email As RA1_Email_ID,  upper(ra2user.first_name + ' ' + ra2user.last_name) [RA2 Name], " +
                       "ra2user.email As RA2_Email_ID, upper(Emp_name) empName, " +
                       "um.email As empEmail_ID, upper(empDesignation) empDesignation, " + 
                       "(select CONVERT(VARCHAR(10),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),101)) " +
                       "as empConfirmation_Date  , " +
                       "convert(varchar(10), em.date_of_Joining, 103) as date_of_Joining " +
                      "from    emp_mst em " +
                       "join user_mst um on um.employee_mkey = em.mkey  " +
                       "left join user_mst ra1user on ra1user.employee_mkey = em.reporting_to  " +
                       "left join user_mst ra2user on ra2user.employee_mkey = em.reporting_to2  " +
                       "join type_mst_a desig on em.new_design_mkey=desig.master_mkey " +
                        "where  em.emp_card_no="